Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to identify Temp Space being used
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_mbfrom v$sort_segment s,
# 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
and a.address (+) = s.sql_address and a.hash_value (+) = s.sql_hash_value and p.name = 'db_block_size' and s.username != 'SYSTEM'
s.sid || ',' || s.serial#, s.username,osuser, a.sql_text, u.tablespace,
Regards,
Rama
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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. -- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 04 2007 - 15:58:29 CDT