SQL Query to Find Out Sessions Using More Temp Space

advertisements

_____________________________________________________________________________________________________________________

Query

set pages 1000 lines 222
col USERNAME for a15
col osuser for a8
col tablespace for a8
col MODULE for a10
SELECT  so_us.TABLESPACE       , 
	so_us.segfile#       , 
	so_us.segblk#       , 
	ROUND (  (  ( so_us.blocks * para.VALUE ) / 1024 / 1024 ), 2 ) size_mb, 
	sess.SID       , 
	sess.serial#       , 
	sess.username       , 
	sess.osuser       , 
	sess.module       ,  
	sess.status
FROM  v$session sess       , 
	v$sort_usage so_us       , 
	v$process pro       , 
	v$parameter para
WHERE para.NAME = 'db_block_size'
AND   sess.saddr = so_us.session_addr
AND   sess.paddr = pro.addr
ORDER BY so_us.TABLESPACE, so_us.segfile#, so_us.segblk#, so_us.blocks;

Sample Output

TABLESPA   SEGFILE#    SEGBLK#    SIZE_MB        SID    SERIAL# USERNAME        OSUSER   MODULE     STATUS
-------- ---------- ---------- ---------- ---------- ---------- --------------- -------- ---------- --------
TEMP           4097        768          1         26      12067                 oracle   MMON_SLAVE ACTIVE
TEMP           4097       1536          1       8747       6219                 oracle   MMON_SLAVE ACTIVE
TEMP           4097       1664          1       6212      28325                 oracle   MMON_SLAVE ACTIVE
TEMP           4097       1792          1       3951      12500                 oracle   MMON_SLAVE ACTIVE
TEMP           4098       1408          1       5081      21515                 oracle   MMON_SLAVE ACTIVE
TEMP           4098       1536          1      10439      54377                 oracle   MMON_SLAVE ACTIVE

_____________________________________________________________________________________________________________________

Website Stats