Monitoring Temporary Space Usage

Monitoring Temporary Space Usage 
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
--------------------------------------------

Sort Space Usage by Session 
--------------------------------------------

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
---------------------------------------

Sort Space Usage by Statement 
---------------------------------------
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
------------------------------------

http://www.dbspecialists.com/presentations/temp_space.html

-----------------------------------

To know the sids which are using TEMP space:
-----------------------------------

set line 120
select rpad(sid,5,' ') sid,s.username,
rpad(program,15,' ') prgm,
rpad(blocks*16384/(1024*1024)||' MB ',10,' ') tempsize,
segtype from v$session s,v$sort_usage v
where s.saddr=v.session_addr
order by blocks ;

-----------------------------------

Users Accessing Temp Tablespace:
-----------------------------------


How can one see who is using a temporary segment?
For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'. 
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment. 

If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT 

From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples: 

 select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
        from   sys.v_$session s, sys.v_$sort_usage u
        where  s.saddr = u.session_addr
        /

        select s.osuser, s.process, s.username, s.serial#,
               sum(u.blocks)*vp.value/1024 sort_size
        from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
        where  s.saddr = u.session_addr
          and  vp.name = 'db_block_size'
          and  s.osuser like '&1'
        group  by s.osuser, s.process, s.username, s.serial#, vp.value
        /

------------------------------------