Temp Tablespace Utilization Monitoring

Monitor temp usage

-------------------------------

   
Globally
========
select a.tablespace_name,
to_char(a.used/1024/1024, 'FM999,990.00')  MB_USED,
to_char(a.total/1024/1024, 'FM999,990.00') MB_TOTAL,
to_char(used*100/total, 'FM990.00')||'%' perc_used from (
  select tablespace_name, block_size
  , (select SUM (v$sort_usage.blocks * block_size) from v$sort_usage
     where v$sort_usage.TABLESPACE = dba_tablespaces.tablespace_name) USED
  , (select sum(bytes) from dba_temp_files where tablespace_name = dba_tablespaces.tablespace_name) TOTAL
  from dba_tablespaces
  where contents = 'TEMPORARY'
) a

************************************************************************************************************

For each session
==============
SELECT (SELECT   nvl(SUM (v$sort_usage.blocks * dba_tablespaces.block_size
                     ), 0)
               / 1024
               / 1024
          FROM v$sort_usage, dba_tablespaces
         WHERE dba_tablespaces.tablespace_name = v$sort_usage.TABLESPACE
           AND v$sort_usage.session_addr = s.saddr) sort_space_mb,
       s.*
  FROM v$session s

****************************************************************************************************************


Total Temp Usage
==============
select (total_blocks*8192)/1024/1024/1000 "Avail (Gb)",(used_blocks*8192)/1024/1024/1000 "Used (Gb)",
(free_blocks*8192)/1024/1024/1000 "Free (Gb)" from v$sort_segment



TEMP usage session level
====================
Temp usage - session level
column username format a15
column tablespace format a10
col sid format 9999
col machine format a12
col last_call_et/3600 format 999.99
col osuser format a15
set pages 1000
set lines 170
select /*+ RULE */ v.sid,v.serial#,v.username,v.osuser,v.machine, v.status,v.last_call_et/3600,tablespace,blocks*8192/1024/1024/1000 "size (GB)"
from v$session v,v$sort_usage s
where v.saddr=s.session_addr and (blocks*8192/1024/1024/1000) > 0.5
order by 9 desc


To See Sort being carried out
=====================
select sid, s.username, tablespace, blocks*8192/1024/1024 from v$session s,v$sort_usage ss where s.saddr=ss.session_addr;

By Prasanth Bhaskaran ( Oracle PeopleSoft Upgrade & DBA Specialist )