Temporary Tablespace Usage by SQL Queries

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.