Creator of Sort Segment in Oracle 8 and above

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