Identify who is using temporary tablespaces?

posted Apr 25, 2012, 6:33 AM by Sachchida Ojha   [ updated Apr 25, 2012, 6:46 AM ]
Oracle uses temporary tablespaces for storing intermediate results from sort operations as well as any temporary tables, temporary LOB's, and temporary B-trees. You can have many temporary tablespaces, but one one default temporary tablespace. If you do not explicitly assign a temporary tablespace, that user is assigned the default temporary tablespace.

Monitor the usage of temp tablespace
SQL> select * from
(select a.tablespace_name,sum(a.bytes/1024/1024) allocated_mb from dba_temp_files a
where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024) used_mb,sum(b.bytes_free/1024/1024)free_mb
from v$temp_space_header b
where b.tablespace_name= upper('&&temp_tsname') group by b.tablespace_name);
Identify who is using temporary tablespaces
SQL>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;
SQL>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, v$process p, dba_tablespaces t
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;