SELECT * FROM dba_temp_free_space; (Oracle11g)
select * from v$tempseg_usage;
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 = sess.sql_address
ORDER BY blocks DESC;