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 dtf.tablespace_name=dt.TABLESPACE_NAME