temp不足了怎么办

定位导致temp消耗过大的sql，优化即可

--检查temp大小
set lin 200 pages 100
col tablespace_name for a20
col FILE_NAME for a40
select tablespace_name,file_name,round(bytes/1024/1024)m from dba_temp_files order by 1,2;

col TABLESPACE_NAME for a20
select TABLESPACE_NAME,file_id,round(BYTES_USED/1024/1024,2) BYTES_USED_mb,
round(BLOCKS_USED*8/1024,2) BLOCKS_USED_m,BYTES_FREE/1024/1024 free_mb from v$temp_space_header;

--检查当前使用情况
col username for a15
col TABLESPACE for a18
col EVENT for a30
select s.sid,s.username,s.state,s.event,s.sql_id,u.tablespace,u.contents,u.segtype,round(((u.blocks*p.value)/1024/1024),2) mb
from v$session s ,v$sort_usage u,v$parameter p
where s.saddr=u.session_addr
and upper(p.name)='DB_BLOCK_SIZE'
order by mb desc;

--检查历史使用情况
select *
from (select instance_number, sql_id, max(temp_sum_mb) temp_max
        from (select instance_number, sample_time, sql_id, round(sum(nvl(temp_space_allocated, 0))/1024/1024) temp_sum_mb
                from dba_hist_active_sess_history
               where sample_time between sysdate-1/24 and sysdate
            group by instance_number, sample_time, sql_id)
       group by instance_number, sql_id
       order by temp_max desc)
where rownum <= 10;

--看看sql 内容
col sql_text for a80
set lin 80 pages 1000
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;

--看看sql计划
set long 1000000
set longchunksize 1000000
set lin 300 pages 1000
set trim on
set trimspool on
set echo off
set feedback off
select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'TEXT',report_level=>'ALL') mon_rpt from dual;

参考：

http://blog.chinaunix.net/uid-20687159-id-5853939.html