Queries to monitor Temporary Tablespace

Temp Usage


  set linesize 120
  col v_size_GB format 99.9999
  col TABLESPACE format a15
  col USERNAME format a15

  SELECT t.USERNAME,
    t.tablespace,
    s.sid,
    spid OS_ID,
    s.serial#,
    ROUND(blocks*8192/1024/1024/1024 , 5) v_size_GB,
    s.logon_time,
    (s.last_call_et/60) Session_Time_Minutes
  FROM v$tempseg_usage t,
    v$session s,
    v$process p
  WHERE t.SESSION_ADDR=s.saddr
  AND p.addr=s.paddr;


Temp USED/FREE 



  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;