For Oracle 8 and above, the new dictionary view "V$SORT_USAGE" shows
currently active sorts for the instance. Joining "V$SORT_USAGE" to
"V$SESSION" will provide the user who is performing a sort within the
sort segment. The CONTENTS column shows whether the segment is
created in a temporary or permanent tablespace.
Creator of Sort Segment in Oracle 8 and above
---------------------------------------------
For Oracle 8 and above, the following query will return all users and their
SIDs which are doing a sort:
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
You will receive the following:
File Block
Tablespace Name ID ID Blocks SID SERIAL# USERNAME OSUSER STATUS
--------------- ------ --------- ---------- ------- ---------- ------------ ------------------------------ --------
TEMP 4 22 289 15 1966 SCOTT usupport ACTIVE