Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT S.sid, S.serial#, SUM (T.blocks) * TBS.block_size / 1024 / 1024 used
_mb, T.tablespace
  2    FROM v$sort_usage T, v$session S, dba_tablespaces TBS
  3   WHERE T.session_addr = S.saddr
  4     AND T.tablespace = TBS.tablespace_name
  5     and s.sid = sys_context('userenv','sid')
  6   GROUP BY S.sid, S.serial#, TBS.block_size, T.tablespace;

no rows selected

SQL> declare
  2    t sys_refcursor;
  3
  4    i number;
  5  begin
  6    open t
  7    for
  8    with rownums
  9    as (select distinct
 10        rownum rn
 11          from dual
 12       connect by level <=1000000
 13    )
 14    select rn from rownums
 15     union all
 16    select rn from rownums;
 17
 18    fetch t into i;
 19    close t;
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> SELECT S.sid, S.serial#, SUM (T.blocks) * TBS.block_size / 1024 / 1024 used
_mb, T.tablespace
  2    FROM v$sort_usage T, v$session S, dba_tablespaces TBS
  3   WHERE T.session_addr = S.saddr
  4     AND T.tablespace = TBS.tablespace_name
  5     and s.sid = sys_context('userenv','sid')
  6   GROUP BY S.sid, S.serial#, TBS.block_size, T.tablespace;

       SID    SERIAL#    USED_MB TABLESPACE
---------- ---------- ---------- -------------------------------
       264       9871         12 MY_TEMP

SQL>  alter system flush buffer_cache
  2  ;

System altered.

SQL>  alter system flush shared_pool;

System altered.

SQL> begin
  2    dbms_session.modify_package_state(DBMS_SESSION.FREE_ALL_RESOURCES);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> alter session set events '60025 trace name context forever';

Session altered.

SQL> SELECT S.sid, S.serial#, SUM (T.blocks) * TBS.block_size / 1024 / 1024 used
_mb, T.tablespace
  2    FROM v$sort_usage T, v$session S, dba_tablespaces TBS
  3   WHERE T.session_addr = S.saddr
  4     AND T.tablespace = TBS.tablespace_name
  5     and s.sid = sys_context('userenv','sid')
  6   GROUP BY S.sid, S.serial#, TBS.block_size, T.tablespace;

       SID    SERIAL#    USED_MB TABLESPACE
---------- ---------- ---------- -------------------------------
       264       9871         12 MY_TEMP

SQL> declare
  2    t sys_refcursor;
  3
  4    i number;
  5  begin
  6    open t
  7    for
  8    with rownums
  9    as (select distinct
 10        rownum rn
 11          from dual
 12       connect by level <=1000000
 13    )
 14    select rn from rownums
 15     union all
 16    select rn from rownums;
 17
 18    fetch t into i;
 19    close t;
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> SELECT S.sid, S.serial#, SUM (T.blocks) * TBS.block_size / 1024 / 1024 used
_mb, T.tablespace
  2    FROM v$sort_usage T, v$session S, dba_tablespaces TBS
  3   WHERE T.session_addr = S.saddr
  4     AND T.tablespace = TBS.tablespace_name
  5     and s.sid = sys_context('userenv','sid')
  6   GROUP BY S.sid, S.serial#, TBS.block_size, T.tablespace;

       SID    SERIAL#    USED_MB TABLESPACE
---------- ---------- ---------- -------------------------------
       264       9871         24 MY_TEMP

SQL>