Query for Temporary Tablespace Usage in Oracle

 

Query to check  Temp tablespace  usage by Sessions

  

SELECT b.tablespace,
       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
       a.inst_id as Instance,
       a.sid||','||a.serial# AS sid_serial,
       NVL(a.username, '(oracle)') AS username,
       a.osuser,
       a.status,
       a.sql_id,
      b.segtype, 
    'alter system kill session '''||a.sid||','||a.serial# ||''''||' ;',
       a.program,
       b.blocks
FROM   gv$session a,
       gv$sort_usage b,
       gv$parameter p
WHERE  p.name  = 'db_block_size'
AND    a.saddr = b.session_addr
AND    a.inst_id=b.inst_id
AND    a.inst_id=p.inst_id
ORDER BY b.tablespace, b.blocks desc;

 Query to check  Temp tablespace usage

 

SELECT A.tablespace_name Tablespace_name,
        round(D.mb_total/1024,2) as TOTAL_GB,
    round(SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024,2) as  GB_Used,
    round((D.mb_total/1024) - SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024,2) as  GB_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