Wednesday, July 22, 2015

Show the TEMP tablespace history of sort usage

Script that will show the TEMP tablespace history of sort usage

select distinct
c.username "user",
c.osuser ,
c.sid,
c.serial#,
b.spid "unix_pid",
c.machine,
c.program "program",
a.blocks * e.block_size/1024/1024 mb_temp_used  ,
a.tablespace,
d.sql_text
from
v$sort_usage a,
v$process b,
v$session c,
v$sqlarea d,
dba_tablespaces e
where c.saddr=a.session_addr
and b.addr=c.paddr
and c.sql_address=d.address(+)
and a.tablespace = e.tablespace_name;