troubleshooting temp space issues in oracle database

Monitoring Temporary Space Usage




SQL> SELECT   A.tablespace_name tablespace, D.mb_total,
  2           SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
  3           D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  4  FROM     v$sort_segment A,
  5           (
  6           SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
  7           FROM     v$tablespace B, v$tempfile C
  8           WHERE    B.ts#= C.ts#
  9           GROUP BY B.name, C.block_size
 10           ) D
 11  WHERE    A.tablespace_name = D.name
 12  GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
------------------------------- ---------- ---------- ----------
TEMP                                  4000          0       4000

SQL>


SQL> set time on timi on
09:07:31 SQL> SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
09:07:59   2           S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
09:07:59   3           COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
09:07:59   4  09:07:59   5  WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
09:07:59   6  09:07:59   7  AND      T.tablespace = TBS.tablespace_name
09:07:59   8  GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
09:07:59   9           S.program, TBS.block_size, T.tablespace
09:07:59  10  ORDER BY sid_serial;

no rows selected

Elapsed: 00:00:00.04
09:07:59 SQL>
09:08:01 SQL> SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
09:08:17   2           T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
09:08:17   3  09:08:17   4  FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
09:08:17   5  WHERE    T.session_addr = S.saddr
09:08:17   6  AND      T.sqladdr = Q.address (+)
09:08:17   7  AND      T.tablespace = TBS.tablespace_name
09:08:17   8  ORDER BY S.sid;

no rows selected

Elapsed: 00:00:00.02
09:08:17 SQL>