Friday, January 17, 2014

Sort Area Usage/Temp Tablespace Usage of an Oracle Session

Below query will picture of Temp Usage of an Oracle Session.


set lines 132
set feedback on
set pages 99

select s.osuser, s.sid, s.serial#,s.username, s.program,
s.client_info,(sum(u.blocks)*vp.value/1024/1024/1024) AS GB_USED, a.sql_text as sql_t
from sys.gv_$session s, sys.gv_$sort_usage u, sys.gv_$parameter vp, sys.gv_$sqlarea a, sys.gv_$process b
where
s.saddr = u.session_addr and
vp.name = 'db_block_size' and
a.address = s.sql_address and
b.addr = s.paddr
group by s.osuser, s.sid, s.serial#,s.username, s.program,s.client_info, vp.value, a.sql_text;


Thanks,
Jyothish

No comments:

Post a Comment