Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to identify Temp Space being used

RE: How to identify Temp Space being used

From: <rama.ari_at_accenture.com>
Date: Wed, 4 Apr 2007 15:58:29 -0500
Message-ID: <2BC5DDFF6EE1054C80EAC80E4D537B00011835A7@AMRXM2113.dir.svc.accenture.com>


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_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-l
Received on Wed Apr 04 2007 - 15:58:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US