Queries For Temporary Space Usage

1)Sort Space Usage by Session

SELECT S.sid || ',' || S.serial# sid_serial, 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
and s.sid=2616
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

2) Monitoring Temporary Space Usage

SELECT A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
         (
         SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM v$tablespace B, v$tempfile C
         WHERE B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

3) Sort Space Usage by Statement

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 gv$sort_usage T, gv$session S, gv$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;