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
s.saddr = u.session_addr and = '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;


No comments:

Post a Comment