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 you’re 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
SQL>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;
SQL>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;