How to check the TEMP Table Space usage
TEMP Tablespace Usage queries :
select total_extents, used_extents, total_extents, current_users, tablespace_name
from v$sort_segment;
select username, user, sqladdr, extents, tablespace from v$sort_usage;
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2),
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;