Monitoring Temp Tablespace Use



My OraFAQ Blog

Contact me

This query shows the current usage of the temporary tablespace.

This is very useful to monitor if you are getting ORA-1652: unable to extend temp segment errors, as well as for monitoring the use of space in your temp tablespace.

select sess.USERNAME, sess.sid, sess.OSUSER, su.segtype, 
su.segfile#, su.segblk#, su.extents, su.blocks
from v$sort_usage su, v$session sess
where sess.sql_address=su.sqladdr and sess.sql_hash_value=su.sqlhash ;

You can compare the EXTENTS and BLOCKS columns above to the total available segments and blocks by querying V$TEMP_EXTENT_POOL.

select * from v$temp_extent_pool;
The EXTENTS_CACHED are the total number of extents cached -- following an ora-1652, this will be the max number of extents available. The EXTENTS_USED column lets you know the total number of extents currently in use.

In a TEMPORARY temporary tablespace (ie. one with a TEMPORARY datafile), all the space in the datafile is reserved for temporary sort segments. (In a well-managed database, this is of course true for non-TEMPORARY temp tablespaces as well).

Extent management should be set to LOCAL UNIFORM with a size corresponding to SORT_AREA_SIZE. In this case, it's easy to see how many temp extents will fit in the temporary tablespace:

SELECT dtf.file_id, dtf.bytes/dt.INITIAL_EXTENT max_extents_allowed 
from dba_temp_files dtf, dba_tablespaces dt
where dtf.tablespace_name='TEMPORARY_DATA' and 

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...