You want monitor the usage of the temporary tablespace
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);
Issue the following query to find out which SQL Statement is using up space management
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;
The output of this query will show you the space that each session is using in the temporary tablespace , as well as the number of sort operations that session is performing right now.
select se.sid,
se.username, se.osuser, pr.spid,
se.module,se.program,
sum (su.blocks) * ts.block_size / 1024 / 1024 mb_used, su.tablespace,
count(*) sorts
from v$sort_usage su, v$session se, dba_tablespaces ts, v$process pr
where su.session_addr = se.saddr
and se.paddr = pr.addr
and su.tablespace = ts.tablespace_name
group by se.sid, se.serial#, se.username, se.osuser, pr.spid, se.module,
se.program, ts.block_size, su.tablespace;
