RE: How to identify Temp Space being used
- From: <rama.ari@xxxxxxxxxxxxx>
- To: <oratips@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Apr 2007 15:58:29 -0500
Bala,
# Over all temp usage
select sysdate dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024 temp_tot_mb,
s.used_blocks*8192/1024/1024 temp_used_mb,
s.free_blocks*8192/1024/1024 temp_free_mb
from v$sort_segment s,
(select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_temp_files
group by tablespace_name) d
where s.tablespace_name=d.tablespace_name;
/
# Temp usage by each user
select s.sid || ',' || s.serial# sid,
s.username,osuser,
u.tablespace,
round(((u.blocks*p.value)/1024/1024),2) size_mb,
a.sql_text
from v$sort_usage u,
v$session s,
v$sqlarea a,
v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
and s.username != 'SYSTEM'
group by
s.sid || ',' || s.serial#,
s.username,osuser,
a.sql_text,
u.tablespace,
round(((u.blocks*p.value)/1024/1024),2)
order by 5 desc
Regards,
Rama
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bala
Sent: Wednesday, April 04, 2007 1:46 PM
To: oracle-l
Subject: How to identify Temp Space being used
Can anyone share the sql to identify the Temp space being used by the
currently running SQL in database
Thanks much.
--
Bala Rao
This message is for the designated recipient only and may contain privileged,
proprietary, or otherwise private information. If you have received it in
error, please notify the sender immediately and delete the original. Any other
use of the email by you is prohibited.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
