temp表空间不足处理思路

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP 
相信这种报错大部分dba都处理过。引起临时表空间增长的原因很多，如order by/group by，数据导入导出，索引维护。

查询思路

temp暴增后，首先应该确认占用temp的详细情况：
从11g开始，ash视图新增TEMP_SPACE_ALLOCATED列，能看到不同时间段的增长情况。

select to_char(sample_time,'yyyymmdd hh24:mi:ss'),session_id,session_serial#,sql_id,max(TEMP_SPACE_ALLOCATED/1024/1024)
from v$active_session_history where con_id=3
and to_char(sample_time,'yyyymmdd hh24')='20200204 16'
group by to_char(sample_time,'yyyymmdd hh24:mi:ss'),session_id,session_serial#,sql_id order by 5;

TO_CHAR(SAMPLE_TI SESSION_ID SESSION_SERIAL# SQL_ID
----------------- ---------- --------------- -------------
MAX(TEMP_SPACE_ALLOCATED/1024/1024)
-----------------------------------

20200204 16:02:08         72           37321 g1dw2r1d4g2am
                                  0

20200204 16:02:06         72           37321 g1dw2r1d4g2am
                                  0

测试环境资源有限，最后一列就是占用的临时表空间大小，可以看到不同时间段temp占用情况。实际生产中，可以看到几分钟内，某条sql_id占用急速上升。
查看sql_id内容：
select * from table(dbms_xplan.display_cursor('&sqlid'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  g1dw2r1d4g2am, child number 0
-------------------------------------
select id,count(*) from t1 group by id

Plan hash value: 136660032

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  5740 (100)|          |
|   1 |  HASH GROUP BY     |      |    13M|   171M|  5740   (9)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL| T1   |    13M|   171M|  5318   (2)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


18 rows selected.

执行计划中可以看到1300w行的group by
select SID,SERIAL#,USERNAME,MACHINE,PROGRAM from v$session where sid=72;
  SID    SERIAL# USERNAME        MACHINE         PROGRAM
----- ---------- --------------- --------------- -------------------------
   72      37321 SYS             localhost.local sqlplus@localhost.localdo
                                 domain          main (TNS V1-V3)

复制

以上信息可以确认，sys用户本地连接（session：’72，37321’），执行1300W行数据的group by语句。

处理思路

增加临时表空间和kill session 可以作为应急处理，解决问题可以考虑以下：
1 、设置合理的pga
2 、禁止业务高峰期执行未上线的sql
3 、优化sort sql