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;