Based on Oracle 10g/11g
-- show summary temp tablespace usage
SELECT t.tablespace_name
,t.total_temp_mb
,u.used_temp_mb
, (t.total_temp_mb - u.used_temp_mb) free_temp_mb
FROM (SELECT tf.tablespace_name
, SUM (DECODE (tf.autoextensible
,'YES', tf.maxbytes
,'NO', BYTES
))
/ 1024
/ 1024 total_temp_mb
FROM dba_temp_files tf
GROUP BY tf.tablespace_name) t
, (SELECT ss.tablespace_name
, SUM (ss.used_blocks)
* (SELECT ts.block_size
FROM dba_tablespaces ts
WHERE ts.CONTENTS = 'TEMPORARY'
AND ts.tablespace_name = ss.tablespace_name)
/ 1024
/ 1024 used_temp_mb
FROM v$sort_segment ss
GROUP BY ss.tablespace_name) u
WHERE t.tablespace_name = u.tablespace_name
-- show temp space usage by session
SELECT su.TABLESPACE
--,su.segtype
, SUM (su.blocks * ts.block_size) / 1024 / 1024 mb
,se.SID
,se.serial#
,se.username
,se.status
,se.osuser
,se.machine
,se.program
,se.action
,sq.sql_text
FROM v$sort_usage su
,v$session se
,dba_tablespaces ts
,v$sql sq
WHERE su.session_addr = se.saddr
AND ts.tablespace_name = su.TABLESPACE
AND ts.CONTENTS = 'TEMPORARY'
AND se.sql_address = sq.address(+)
AND se.sql_hash_value = sq.hash_value(+)
GROUP BY su.TABLESPACE
,se.SID
,se.serial#
,se.username
,se.status
,se.osuser
,se.machine
,se.program
,se.action
,sq.sql_text
ORDER BY 2 DESC;
Ref:
1. Script - Temporary tablespace usage
http://gavinsoorma.com/2009/06/temp-tablespace-usage/