Checking Temporary Tablespace Usage in Oracle

December 24, 2018

Temporary tablespaces often referred as Temp tablespace in Oracle are used for sorting operations and can grow huge in size. So you need to monitor temp tablespace utilization including total size, used and free space. It is also used for storing global temporary tables and joining operations and its growth depends on how much data you are sorting and joining.

Temp tablespace usage and free space.
How to Shrink Temp Tablespace and Reclaim Unused Space in Oracle

If temp tablespace is full then you will get the error “ORA-1652: unable to extend temp segment by 128 in tablespace TEMP”

Query to check Temp Tablespace 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;

Temp Tablespace usage by session in Oracle.

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
          P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
          COUNT(*) statements
 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,
          P.program, TBS.block_size, T.tablespace
 ORDER BY sid_serial;

Must Read: