Get Temporary Tablespace Usage

SELECT A.tablespace_name tablespace,D.mb_total,SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total-SUM (A.used_blocks * D.block_size)/1024/ 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size) D  WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;


TABLESPACE MB_TOTAL MB_USED MB_FREE
——————————- ———- ———- ———-
TEMP2 27000 80 26920

Get Current TEMP space usage by session id (9i/10g):
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;