As more often we’ve noticed that temp usage hits max in certain situations and in order for DBA to proactively monitor and get notified, DBA can schedule script to monitor temp space usage and here is the sample script that can scheduled to run more often ( probably every 15 or 10 mins frequency depending on load of the system you support)

 

 

SET TERMOUT OFF

STORE SET monitor_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

/

@monitor_env.sql

host rm monitor_env.sql

SET TERMOUT ON