Temp usage

SELECT  s.username, s.sid, s.serial#, s.osuser,
u.tablespace, u.contents, u.segtype, u.sql_id,  u.extents
, u.blocks, t.block_size*u.blocks/1024/1024 MB,
sum(t.block_size*u.blocks/1024/1024) over ( partition by s.username ) user_total,
sum(t.block_size*u.blocks/1024/1024) over (  ) total , max(segblk# ) block_hwm, max(segblk# ) * t.block_size /1024/1024 MB_HWM ,  f.name  file_name
FROM v$session s
inner join v$sort_usage u on s.saddr=u.session_addr
inner join dba_tablespaces t   on  u.tablespace = t.tablespace_name
left outer join v$tempfile f  on u.segrfno#  = f.rfile#
group by  s.username, s.sid, s.serial#, s.osuser,u.tablespace, u.contents, u.segtype, u.sql_id, t.block_size, u.extents,  u.blocks,   f.name
order by f.name, s.username, s.osuser;

Updated to include sql_id ( may be buggy - see http://kerryosborne.oracle-guy.com/2010/01/temp-usage/)

Including HWM for session/tempfile - because if you're running this, you're looking for sessions to kill.