Lesson 8	Monitoring the TEMP Tablespace
Objective	Monitor temporary segments in the TEMP tablespace

Monitoring the TEMP Tablespace

As we discussed in a prior lesson, you can watch Oracle step through the execute phases of an SQL statement. The last step of a SQL statement that does an ORDER BY on a large result set is the sort step, and you can watch Oracle allocate temporary segments to service a disk sort.

View the code below to see a script that will display all temporary segments in the TEMP tablespace.

set pagesize 60 linesize 132 verify off
break on file_id skip 1
column file_id heading "File|Id"
column tablespace_name for a15
column object          for a15
column owner           for a15
column MBytes          for 999,999
select tablespace_name,
       'free space' owner,   
       '   '        object,  
       file_id,              
       block_id,             
       CEIL(blocks*4/1024) MBytes   
  from dba_free_space
 where tablespace_name like '%$1%'
union
select tablespace_name,
       substr(owner, 1, 20),        
       substr(segment_name, 1, 32),  
       file_id,                     
       block_id,                    
       CEIL(blocks*4/1024) MBytes   
  from dba_extents
 where tablespace_name like '%$1%'
 order by 1, 4, 5

Temporary Segments

Notice the temporary segments (with numerical names like 23.4502) and their size within the TEMP tablespace. While you cannot control how Oracle allocates segments within the TEMP tablespace, this query can be very useful when watching long-running queries, since you can tell by the presence of TEMP segments when the query enters the sorting phase.
An Oracle explain plan does not say whether a sort will be done in-memory or to disk, and the only way to know for sure is to monitor the query with SQL*Trace, or to monitor the TEMP tablespace. In most cases, the DBA will monitor the TEMP tablespace and attempt to locate and remedy large sort operations. Now let us look at techniques for removing sorts altogether from SQL.

I want to monitor my TEMP tablespace growth and usage and want to see TEMP objects space as they are created and deleted from my TEMP tablespace.
Question: How can I monitor the activity within my TEMP tablespace?
Answer: There are several ways to monitor TEMP tablespace usage.

real time TEMP tablespace monitoring with dba_extents, and
long term TEMP tablespace usage with AWR table dba_hist_tbspc_space_usage which has a tablespace_usedsize column.

You can use the dba_extents view to see recent TEMP usage activity

set pagesize 10000
set linesize 133

column tablespace_name format a10         heading 'Tablespace|Name' 
column file_id         format 9,999       heading 'File|ID' 
column block_id        format 999,999,999 heading 'Block|ID' 
column blocks          format 999,999,999 heading 'Blocks' 
column segment_name    format a15         heading 'Segment Name'
column segment_type    format a15         heading 'Segment Type'

break on tablespace_name skip 1 on file_id skip 1

select 
   tablespace_name, 
   file_id, 
   segment_name,
   segment_type, 
   block_id, 
   blocks 
from 
   dba_extents 
 where 
   tablespace_name = 'TEMP'
order by 
   file_id, 
   block_id;