Check which SQL statement is using Temporary Tablespace

Temporary tablespace is used for Sorting the data of table by sort segments.

Find out the SQL Statement using Temporary table space as operation

During running the SQL statements having order by clause, having join in between then oracle use the temporary table space for sorting purpose, if table size is larger than the memory allocated size then oracle use temporary space for getting result. It is kind of physical read from the hard disk. So, its better to avoid this kind of situation by tuning SQL queries

Following query give you the SQL statement and session id which is using Temporary tablespace in Oracle:

select se.sid, se.username,su.blocks * ts.block_size / 1024 / 1024 mb_used, su.tablespace,su.sqladdr address, sq.hash_value, sq.sql_text from v$sort_usage su, v$session se, v$sqlarea sq, dba_tablespaces ts where su.session_addr = se.saddr and su.sqladdr = sq.address (+) and su.tablespace = ts.tablespace_name;