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

select s.inst_id,
       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 gv$sort_usage t, gv$session s, dba_tablespaces tbs, gv$process p
 where t.inst_id = s.inst_id
   and s.inst_id = p.inst_id
   and t.session_addr = s.saddr
   and s.paddr = p.addr
   and t.tablespace = tbs.tablespace_name
 group by s.inst_id,
          s.sid,
          s.serial#,
          s.username,
          t.tablespace,
          s.osuser,
          p.spid,
          s.module,
          p.program,
          tbs.block_size
 order by s.inst_id, used_mb desc;