Using the Temporary Tablespace

Oracle try to use sort and hash operation in memory(PGA). Suppose if operation is too  long, it  use temporary tablespace. Even single  transaction try to use full temporary tablespace.  Once temp tablespace is full other session ‘s SQL also try to use  temp tablespace.

So we can get ORA error ORA-01652: unable  to extend temp  segment error.

     1. New Sessions may not be able to connect.

     2. Queries can sometimes hang.

     3. Users may  not able to issue new query.

If temp tablespace is full, transaction won’t   complete. We can find error into alert log file also.

Use below query to fine out which SQL statment is using Temporay Tablespace.

 select s.sid || ',' || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,

o.sqladdr address, h.hash_value, h.sql_text

from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t

where o.session_addr = s.saddr and o.sqladdr = h.address (+) and o.tablespace = t.tablespace_name  order by s.sid;

Below query find out which session are using space in the memory.

select s.sid || ',' || s.serial# sid_serial, s.username, s.osuser, p.spid,

s.module,s.program,sum (o.blocks) * t.block_size / 1024 / 1024 mb_used, o.tablespace,count(*) sorts from v$sort_usage o, v$session s, dba_tablespaces t, v$process p where o.session_addr = s.saddr

and s.paddr = p.addr and o.tablespace = t.tablespace_name group by s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,s.program, t.block_size, o.tablespace  order by sid_serial;