Temporary Tablespace Usage

v$sort_usage dynamic performance views deprecated in 9.2

 http://docs.oracle.com/cd/B19306_01/server.102/b14238/changes.htm#i639263

can use V$TEMPSEG_USAGE to obtain temp segment usage by session.

col machine format a20
col osuser format a20

select s.sid,s.username, s.osuser, s.machine, sum(u.extents) from V$TEMPSEG_USAGE u, v$session s
where s.saddr=u.session_addr
group by sid,s.username, s.osuser, s.machine
order by 5 desc;


       SID USERNAME                       OSUSER               MACHINE              SUM(U.EXTENTS)
---------- ------------------------------ -------------------- -------------------- --------------
      1718 APPUSER                        orauser2             node1                          7561
      2170 RAMAZAN                        rozturk              DOMAIN\PCRAMAZAN                120
      3358 OTHERUSER                      SYSTEM               node3                            11