Temp usage script

    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 ;