Below are some of the queries related to Temp Tablespace :
To find Total Free space in Temp Tablespace
select ‘FreeSpace ‘ || (free_blocks*8)/1024/1024 ||’ GB’ from v$sort_segment where tablespace_name=’TEMP’;
select tablespace_name , (free_blocks*8)/1024/1024 FreeSpaceInGB,
(used_blocks*8)/1024/1024 UsedSpaceInGB,
(total_blocks*8)/1024/1024 TotalSpaceInGB from v$sort_segment where tablespace_name like ‘%TEMP%’ ;
To find Total Space Allocated for Temp Tablespace
select ‘TotalSpace ‘ || (sum(blocks)*8)/1024/1024 ||’ GB’ from dba_temp_files where tablespace_name=’TEMP’;
Identifying WHO is currently using TEMP Segments
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name=’TEMP’) d
WHERE b.tablespace = ‘TEMP’
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND (b.blocks*d.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;