Script to find the temp usage
SELECT S.sid,S.serial# sid_serial,S.INST_ID, S.username, S.osuser, P.spid, S.module,S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.INST_ID,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
To get the SQL running in the temp tablespace
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
How Do You Find Who And What SQL Is Using Temp Segments (Doc ID 317441.1)
SELECT a.username, a.sid, a.serial#, a.osuser, a.program, a.module, d.spid, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c, v$process d
WHERE a.saddr = b.session_addr
AND a.paddr= d.addr
AND c.sql_id = b.SQL_ID_TEMPSEG
ORDER BY b.tablespace, b.blocks;
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
