Identify The Top Temp Consumers In DataBase

First of all one needs to identify the sessions using temp space. The view reflecting this info is v$tempseg_usage. 

One imp note is that v$tempseg_usage is same as v_$sort_area_usage view

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

The main segment type one needs to check is SORT segment as that is what we are after. 
Now we have session that is causing strain on our database sort area we need to check the statement causing all the mayhem. If you are in luck there might be only open cursor against this session. However, in heavily used production databases there will be multiple open cursors against one session. 
So the view you need to consult is v$open_cursor. We also need to identify the number of child cursor for this parent cursor as well as text. Following is the way to do it..


select sql_id, sorts ,  sql_text from gv$sql 
where hash_value in 
(select hash_value from gv$open_cursor where sid=1920)
order by 2 desc;

As you can see there are possible two possible sql that are using temp at the moment. Now you have identified the possible culprits you can investigate further. 


Following Script will tell you how much free space you have available in Temp Tablespace.
SQL> select b.tablespace_name, sum(b.bytes_used/1024/1024) used_mb,
     sum(bytes_free/1024/1024) free_mb
     from v$temp_space_header b
     group by b.tablespace_name;