TEMP tablespace usage history

V$TEMPSEG_USAGE

SELECT username,
       session_addr,
       session_num,
       sqladdr,
       sqlhash,
       sql_id,
       contents,
       segtype,
       extents,
       blocks
FROM   v$tempseg_usage
ORDER BY username;

Script to display the recent activity

select distinct
c.username "user",
c.osuser ,
c.sid,
c.serial#,
'alter system kill session ''' || c.sid || ',' || c.serial# || ''' immediate;' kill_session_cmd,
b.spid "unix_pid",
c.machine,
c.program "program",
a.blocks * e.block_size/1024/1024 mb_temp_used  ,
a.tablespace,
d.sql_text
from
v$tempseg_usage a, /* v$sort_usage */
v$process b,
v$session c,
v$sqlarea d,
dba_tablespaces e
where c.saddr=a.session_addr
and b.addr=c.paddr
and a.sqladdr=d.address(+)
and a.tablespace = e.tablespace_name
order by mb_temp_used desc;

TEMP tablespace usage history

A views V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY include temp_space_allocated field.
Script below showing last 7 days top 5 SQLs for each day of temp tablespace consumers:

select t.sample_time, t.sql_id, t.temp_mb, t.temp_diff
       ,s.sql_text
  from (
        select --session_id,session_serial#,
               --'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd,
               trunc(sample_time) sample_time,sql_id, sum(temp_mb) temp_mb, sum(temp_diff) temp_diff
               , row_number() over (partition by trunc(sample_time) order by sum(temp_mb) desc nulls last) as rn
          from (
                select sample_time,session_id,session_serial#,sql_id,temp_space_allocated/1024/1024 temp_mb, 
                       temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff
                 --from dba_hist_active_sess_history 
                 from v$active_session_history
                where 1 = 1 
                -- session_id=&1 
                -- and session_serial#=&2
               )
         group by --session_id,session_serial#,
                  trunc(sample_time),
                  sql_id
       ) t
  left join v$sqlarea s
    on s.sql_id = t.sql_id
 where 1 = 1
   and rn <=5
   and sample_time >= trunc(sysdate) - 7                 
 order by sample_time desc, temp_mb desc

To release TEMP space usage before shrinking temporary tablespace use:

select distinct
       session_id,session_serial#,
       'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd
  from v$active_session_history
 where 1 = 1
   and sql_id='&3'