select * from (
select sess.SID, segtype, blocks*8/1000 "MB" ,sql_text
from v$sort_usage sort, v$session sess ,v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sort.SQLADDR (+)
order by blocks desc
) a
where rownum<10

Take a look at
v$temp_space_header
v$temp_extent_pool

用完的extent会cache在v$temp_extent_pool中被重用.

v$sort_usage tells you space currently being used.

The query below would show temp usage:

Select round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,
d.file_name "Datafile name",
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB",
round((f.bytes_free + f.bytes_used) / 1024, 2) "total KB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024, 2) "Free KB",
round(nvl(p.bytes_used, 0)/ 1024, 2) "Used KB",
0 "Fragmentation Index"
from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id