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, one can just query SYS.v$sort_usage.
