Monitor the temporary tablespace utilization script

 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;

Joining "V$SORT_USAGE" to "V$SESSION" will provide the user who is 

performing a sort within the sort segment.

The CONTENTS column shows whether the segment is created in a temporary 

or permanent tablespace.

When there are no active sorts for the instance you will not see any 

rows in v$sort_usage.

According to bug 1111185 (closed as not a bug) the BLOCKS_FREE in 

V$TEMP_SPACE_HEADER can be misleading.

"The extents are free but cached (as shown by v$temp_extent_pool).

You can look at this situation as analagous to the blocks being 

formatted rather than unformatted.

Once extents are used, they remain and don't participate in the 

bytes_free, blocks_free calculations of v$temp_space_header, at least 

for locally managed tablespaces."

Instead, you should look at FREE_BLOCKS from v$sort_segment.

the problem is same with the OEM also, it reports incorrectly.