Monitoring TEMP Tablespace Usage

 -- The following query will return all users and their SIDs which are doing a sort  
 SELECT  b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,   
      a.username, a.osuser, a.status  
 FROM   v$session a,v$sort_usage b  
 WHERE  a.saddr = b.session_addr  
 ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;  

 -- Find Who And What SQL Is Using Temp Segments  
 SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text  
 FROM v$session a, v$tempseg_usage b, v$sqlarea c  
 WHERE a.saddr = b.session_addr  
 AND c.address= a.sql_address  
 AND c.hash_value = a.sql_hash_value  
 ORDER BY b.tablespace, b.blocks;  
 NOTE (1): Indications are that the SQL retrieval does not work for parallel query slaves … only the parent process  
 NOTE (2): This query will not assist in determination of the amount of space consumed in a temporary tablespace … if this is desired … then other queries on v$sort_usage or v$tempseg_usage ... should be used.  

 -- Another query for the same check  
 SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text  
 FROM v$session a, v$sort_usage b, v$sqlarea c  
 WHERE b.tablespace = 'TEMP_FCRG'  
 and a.saddr = b.session_addr  
 AND c.address= a.sql_address  
 AND c.hash_value = a.sql_hash_value;  
 AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024;  

 -- Check space usage  
 select  b.Total_MB,  
   b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,  
   round(used_blocks*8/1024)        Current_Used_MB,  
   round(max_used_blocks*8/1024)       Max_used_MB  
 from  v$sort_segment a,  
   (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;