查询当前实例，当前占用temp空间会话：

PS：prev_sql_id,sql_id不一定就是消耗了temp的sql，因为有可能会话曾经执行过消耗了大量临时表空间的SQL，然后后续有执行了其他SQL，这个”其他SQL“就会出现在prev_sq_id(上条SQL)，sql_id(当前SQL)。可以结合v$open_cursor来慢慢确定。

set line 300
col username for a25
col tablespace for a10
col sql_id for a13
col prev_sql_id for a13
col SEGTYPE for a10
col contents for a10
select s.sid,
s.serial#,
s.username,
s.sql_id,
s.prev_sql_id,
--u.sql_id,
u.tablespace,
u.contents,
u.segtype,
u.extents,
u.blocks,
round(((u.blocks * P.VALUE) / 1024 / 1024), 2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P
where s.saddr = u.session_addr
AND UPPER(P.NAME) = 'DB_BLOCK_SIZE'
order by MB DESC;

查询历史占用temp语句：

select instance_number,to_char(a.sample_time,'yyyy-mm-dd hh24:mi:ss') sample_time,sum(temp_space_allocated)/1024/1024 mb,sql_id
from dba_hist_active_sess_history a
where a.temp_space_allocated is not null and a.sample_time between to_date('2020-10-27 12:49:00','yyyy-mm-dd hh24:mi:ss') and to_date('2020-10-27 12:51:00','yyyy-mm-dd hh24:mi:ss')
group by instance_number,to_char(a.sample_time,'yyyy-mm-dd hh24:mi:ss'),sql_id
order by 1,2;

更详细的参考：https://www.cnblogs.com/PiscesCanon/p/13896643.html