Monitor Temp Space

--
-- Title :       montemp.sql 
-- Description : To monitor TEMP space usage. 
-- 
-- Usage/Notes : 
-- 
-- Copyright :   ABCdba.com 2011 
-- 
 
SET TERMOUT OFF 
STORE SET save_env.sql REPLACE 
SET TERMOUT ON 
 
CLEAR BREAKS 
CLEAR COLUMNS 
CLEAR COMPUTES 
 
SET PAGES 20 
SET WRAP ON 
 
COL tablespace FOR a10
COL inst_id FOR  99
COL sid FOR  99999
COL serial# FOR  999999
COL username FOR a24
COL machine FOR a32
COL program FOR a10
 
SELECT su.tablespace, s.inst_id, s.sid, s.serial#, s.username, s.machine, s.program,
       SUM(su.blocks) * ts.block_size/1024/1024 used_MB, count(1) num_sorts
FROM   gv$sort_usage su, gv$process p, gv$session s, dba_tablespaces ts
WHERE  su.session_addr = s.saddr
AND    s.paddr = p.addr
AND    su.tablespace = ts.tablespace_name
GROUP BY su.tablespace, s.inst_id, s.sid, s.serial#, s.username, s.machine, s.program,
       ts.block_size
ORDER BY su.tablespace, s.machine, s.program
/
 
SELECT ss.tablespace_name,
       SUM(ss.used_blocks * il.block_size)/1024/1024 used_MB,
       (il.total_MB - SUM(ss.used_blocks * il.block_size)/1024/1024) free_MB,
       il.total_MB
FROM   gv$sort_segment ss,
       (SELECT ts.name, tf.block_size, SUM(tf.bytes)/1024/1024 total_MB
        FROM   gv$tablespace ts, gv$tempfile tf
        WHERE  ts.ts#= tf.ts#
        GROUP BY ts.name, tf.block_size) il
WHERE  ss.tablespace_name = il.name
GROUP BY ss.tablespace_name, il.total_MB
/
 
@save_env.sql 
host rm save_env.sql 
SET TERMOUT ON