Where is the TEMP space used?

The following statements should give us more indication about the TEMP usage and what is using it:

col username format a10
col osuser format a8
col SID_SERIAL format a8
col PROCESS format 9999999
col tablespace format a7
col status format a9
col size_mb format 9999999999999
set lin 300

SELECT   b.TABLESPACE
       --, b.segfile#
       --, b.segblk
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb 
       , a.SID
       , a.serial#
       , a.username
       , a.sql_id
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     AND a.status='ACTIVE'
     AND to_number(ROUND(((b.blocks * 8192)/1024/1024),2))>100
ORDER BY size_mb
       , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;
       
col username format a10
col osuser format a8
col SID_SERIAL format a8
col PROCESS format 9999999
col tablespace format a5
col status format a9
col space format 9999999999999
set lin 300
SELECT a.username, a.osuser, a.sid||','||a.serial# SID_SERIAL, c.spid Process,b.tablespace tablespace, a.status, sum(b.extents)* 1024*1024 space
FROM     v$session a,v$sort_usage b, v$process c, dba_tablespaces d
WHERE    a.saddr = b.session_addr
AND      a.paddr = c.addr
AND      b.tablespace=d.tablespace_name
AND      d.tablespace_name='&temp_tbs'
AND     a.status='ACTIVE'
group by a.username, a.osuser, a.sid||','||a.serial#, c.spid,b.tablespace, a.status
order by space;

Leave Comment

Your email address will not be published. Required fields are marked *