Query To Monitor High Temporary Tablespace Usage Sessions

Download(tempsessions.sql)

REM +======================================================================+
REM                    
REM File Name: tempsessions.sql
REM 
REM Description:
REM   Query To Monitor High Temporary Tablespace Usage Sessions 
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @tempsessions.sql 
REM 
REM Input Required:
REM    SizeInMB : TEMP Usage Size(MB) For Session
REM   
REM +======================================================================+

ACCEPT SizeInMB PROMPT "Enter TEMP Usage Size(MB) For Session: ";

clear columns
set lines 180
set pages 50
Col TABLESPACE for a12
Col USERNAME for a10
col OSUSER for a20
col MODULE for a20 WORD_WRAPPED
col PROGRAM for a20 WORD_WRAPPED
col MACHINE for a15
col inst_id for 99999
select * from (
SELECT   T.tablespace,S.inst_id,S.SID,S.serial#,S.username, S.osuser, s.module,
     S.program,S.MACHINE , SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, 
     COUNT(*) sort_ops
FROM     gv$sort_usage T, gv$session S, dba_tablespaces TBS, gv$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.inst_id,S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.client_identifier,
     S.program, TBS.block_size, T.tablespace,S.MACHINE)
         where MB_USED > &SizeInMB;

undef SizeInMB