–When an Oracle db is running out of temporary tablespace and you need to find out which SQL is using the temp tablespace.

**First, check the temp space usage:

select y.Total_MB, y.Total_MB – round(x.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 x,(select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) y;
** Then,let’s try and fish out who is using the temp segments

col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000

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

–As you may see,segment types differ. Most of the time, SORT is the one we need to check.
–If you are lucky enough, or the client only has 1 SQL cursor, you will get the SQL hash value in the above result.
–NB: the value of x can be got from the sid of the session with the highest sorted blocks from the previous previous query while y is the schema’s name

–However, the tempspace can be used by any open cursor in that session. The current SQL is not necessarily the culprit. In that case, we can check it from v$sql using the query below:

col hash_value for 999999999999
select hash_value, sorts, rows_processed/executions from v$sql where hash_value in (select hash_value from v$open_cursor where sid=x) and sorts > 0 and PARSING_SCHEMA_NAME=’y’ order by rows_processed/executions desc;