如果您已安装了诊断程序包并启用了历史记录AWR集合，则可以从几个地方获取此信息：

1. DBA_HIST_TBSPC_SPACE_USAGE显示历史表空间使用情况统计信息。
2. 从11GR2开始，使用dba_hist_active_sess_history.TEMP_SPACE_ALLOCATED查看会话/ SQL对TEMP的使用

示例1的代码：

 with temp_blocksize as 
(
SELECT B.ts#,b.name,C.block_size,SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B,v$tempfile C
WHERE 1=1
and B.ts#= C.ts# (+)
and b.name like 'TEMP%'
GROUP BY B.ts#,C.block_size
)
select * from 
(
select tablespace_id,name Tablespace_name,(tablespace_maxsize * block_size ) / 1024 / 1024 tablespace_maxsize_mb,round((tablespace_size * block_size) / 1024 / 1024 / 1024,2) tablespace_size_gb,round((tablespace_usedsize * block_size) / 1024 / 1024/ 1024,2) tablespace_usedsize_gb,tablespace_size,tablespace_usedsize,round((tablespace_usedsize/tablespace_size)*100,2) used_pct,rtime,awr.snap_id
from dba_hist_tbspc_space_usage where 1=1
   and awr.tablespace_id = blk.ts#
)
where used_pct > = nvl(:used_pct,0)
          and (to_number(to_char(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'),'HH24')) between nvl(:begin_hour,0) and  nvl(:end_hour,24) 
            or to_number(to_char(to_date(rtime,'HH24')) between nvl(:begin_hour2,nvl(:begin_hour,0)) and  nvl(:end_hour2,nvl(:end_hour,24)))

order by  rtime desc
;

示例2的代码：

    with tmp_usage as
(
select sql_id,sql_plan_hash_value,session_id,session_serial#,min(user_id) user_id,min(sample_time) min_sample_time,max(sample_time) max_sample_time,max(sample_time) - min(sample_time) delta_time,min(snap_id) min_snap_id,max(snap_id) max_snap_id,round(max(temp_space_allocated) / (1024 * 1024)) Temp_usage_meg,round(max(pga_allocated) / (1024 * 1024)) PGA_usage_meg,sum(decode(event,'direct path read temp',1,0) ) cnt_direct_path_read_temp,'direct path write temp',0) ) cnt_direct_path_write_temp,null,0) ) cnt_ON_CPU,count(*) - sum(decode(event,0) ) - sum(decode(event,0) ) cnt_other,count(*) cnt_sample
from dba_hist_active_sess_history ash
where temp_space_allocated is not null
  and session_id = nvl(:session_id,session_id)
  and session_serial# = nvl(:session_serial#,session_serial#)
  and trunc(sample_time,'MI') between to_date(nvl(:sam_tm_str_MM_DD_YYYY_HH24_MI,to_char(sample_time,'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI') 
                      and to_date(nvl(:sam_tm_end_MM_DD_YYYY_HH24_MI,'MM_DD_YYYY_HH24_MI')
  and decode(:today_only_Y_N,'Y',sample_time,trunc(sysdate) ) >= trunc(sysdate)
  and nvl(upper(module),'x') like nvl(upper(:module),nvl(upper(module),'x')) 
  and nvl(machine,'x') like nvl(:machine,nvl(machine,'x')) 
  and nvl(program,'x') like nvl(:program,nvl(program,'x')) 
  and nvl(sql_id,'x') = nvl(:sql_id,nvl(sql_id,'x'))
group by sql_id,session_serial#
order by max(temp_space_allocated) desc
)
select tmp.*,(select username from dba_users du where du.user_id = tmp.user_id) username,(select max(DBMS_LOB.SUBSTR(sql_text,3800)) from dba_hist_sqltext st where st.sql_id = tmp.sql_id) sql_text
from tmp_usage tmp
where 1=1
  and rownum <= nvl(:top_n,10)
order by 
case when :ord_temp = 'TEMP' then Temp_usage_meg 
     when :ord_temp = 'SNAP' then min_snap_id 
     else  Temp_usage_meg 
end 
desc
;

如果您没有Diagnostics Pack许可证，则可以从v $ sort_usage获取信息，但是必须定期保存信息以获取历史报告。