--This script will use v$sort_usage and v$session to show the TEMP tablespace usage for a specific session in single instance DB:

select s.sid,
       s.serial#,
       s.username,
       t.tablespace,
       s.osuser,
       p.spid,
       s.module,
       p.program,
       sum(t.blocks) * tbs.block_size / 1024 / 1024 used_mb
  from v$sort_usage t, v$session s, dba_tablespaces tbs, v$process p
 where t.session_addr = s.saddr
   and s.paddr = p.addr
   and t.tablespace = tbs.tablespace_name
 group by s.sid,
          s.serial#,
          s.username,
          t.tablespace,
          s.osuser,
          p.spid,
          s.module,
          p.program,
          tbs.block_size
 order by used_mb desc;