Query to find the session consuming high Temp TableSpace

Check the Size of the current Temp usage

select tablespace_name, tablespace_size/1024/1024 "Total Space MB",allocated_space/1024/1024 "Alloc Space MB",free_space/1024/1024 "Free Space MB"
from dba_temp_free_space;

Based on the above output run below. I have run for a  temp tablespace TEMP2.

 SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||' or '||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
and u.tablespace='TEMP2'
order by u.blocks desc;


The Top row given will have the sid for the session holding maximum space. Based on that fine more info of the session. Click Here for the Session query


Find based on parsing schema name when the open cursor is used.

select hash_value, sorts, rows_processed/executions
 from v$sql
 where hash_value in (select hash_value from v$open_cursor where sid=1121)
 and sorts > 0
 and PARSING_SCHEMA_NAME='APPS'
 order by rows_processed/executions;





If you like please follow and comment