SQL Query to Find Out Sessions Using More Temp Space

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
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

_____________________________________________________________________________________________________________________

0 comments:

Post a comment

 

acehints.com Copyright 2011-20 All Rights Reserved | Site Map | Contact | Disclaimer