问题描述
查看告警日志看到报错：
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
问题解决
常规检查ORA报错三部曲
第一部：检查Alert Log
告警日志中显示的报错与上述内容一致，并无过多内容可以追踪，但是可以确定的是，报错信息显示异常原因为temp表空间无法扩展
针对上述信息可以确认下一步动作：
1.确认temp tablespace空间大小
2.确认该表空间是否开启自动扩展
3.如果确认已开启自动扩展或已知表空间大小，则判断当时数据库的动作有无超过temp最大空间，即可定位至异常的原因
查看temp表空间大小
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 file_size,autoextensible from dba_temp_files;TABLESPACE_NAME FILE_NAME FILE_SIZE AUT------------------------------ -------------------------------------------------- ---------- ---TEMP +RACDB_DATA/racdb/tempfile/temp.321.96650782110 NOTEMP +RACDB_DATA/racdb/tempfile/temp.438.97146260310 NOTEMP +RACDB_DATA/racdb/tempfile/temp01.dbf 10 NOTEMP +RACDB_DATA/racdb/tempfile/temp.360.96650781710 NO
可以看出temp表空间下有4个数据文件，每个大小为10G，且为开启自动扩展
目前temp表空间总大小为40G
错误定位
根据配置可知，数据库单次获取的数据量应该小于40G，否则会出现temp表空间不足的情况
查询在异常时间内（-06-23 10:00至12:00），占用资源超过40G的SQL
TEMP_SPACE_ALLOCATED >=42949672960
SQL> select *from(select INSTANCE_NUMBER iid,to_char(SAMPLE_TIME,'mmdd hh24:mi:ss') SAMPLE_TIME,to_char(SQL_EXEC_START,'mmdd hh24:mi:ss') SQL_EXEC_START,SESSION_ID sess,SESSION_SERIAL# serial#,machine,sql_id,round(TEMP_SPACE_ALLOCATED/1024/1024/1024) GB,round(TEMP_SPACE_ALLOCATED/1024/1024)-lag(round(TEMP_SPACE_ALLOCATED/1024/1024),1,0) over (partition by INSTANCE_NUMBER,SESSION_ID,SESSION_SERIAL#,machine,program,SQL_EXEC_START order by SAMPLE_TIME) difffrom dba_hist_active_sess_historywhereSAMPLE_TIME between TO_TIMESTAMP ('-06-23 10:00:00', 'YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP ('-06-23 12:00:00', 'YYYY-MM-DD HH24:MI:SS')and TEMP_SPACE_ALLOCATED >=42949672960order by INSTANCE_NUMBER,SAMPLE_TIME,SQL_EXEC_START)where diff >=1 order by GB desc;未选定行
没有查询到结果。扩大了时间范围从8:00到12:00，还是没有结果。
否则，可以根据查询出来的sql_id查询对应的SQL
SQL> select * from dba_hist_sqltext where sql_id='xxx';
那么只能通过第二种方法：
生成AWR报告
告警日志的报错信息是12点左右，所以awr报告生成时间从11点-12点
可以看到发生了等待事件direct path read和direct path read temp
查看引起物理读的前几名SQL
查看导致direct path write等待事件的sql
SQL> select sql_id, count(*) from gv$session where event = 'direct path write'group by sql_id order by count(*) desc; 2 未选定行
SQL> select sql_id, count(*) from dba_hist_active_sess_historywhere event = 'direct path write'group by sql_id order by count(*) desc; 2 3 SQL_ID COUNT(*)------------- ----------1gz69fj5z6kr6471hvdu8dztxpfx411s5dj6fckb73s16bt08vmahnwsm3 8fp700pqw3yy00 5b97wpwpmcuuvz 54wu7fmh7wcqqx 5brntjyy188uk7 49g2rwr603usa7 40jfyv1q7m378w 32pxux7fu9tg7r 37spktz0yr1gxa 2gayvdbrghw368 26fzzh1nqjc4t4 25f4k63fgy7t1r 228u5159x70k02j 1dmhqjx16c6w8y 1956mwc1vc2ky6 19ycqu1mdf7svy 1bqpnkpzk4bt2y 14k46mt51n7n8f 11arqnr7put41x 1cmvrcbkpc2px3 15bzr32d7rya4f 147scxjyac9u03 1已选择26行。
直接查看导致direct path write temp等待事件的sql
SQL> select sql_id, count(*) from gv$session where event = 'direct path write temp'group by sql_id order by count(*) desc; 2 未选定行
SQL> select sql_id, count(*) from dba_hist_active_sess_historywhere event = 'direct path write temp'group by sql_id order by count(*) desc; 2 3 SQL_ID COUNT(*)------------- ----------cqg6vfbga9q95120h95w07ddcg2u 5a9q2hvagvvs81 3dc9g07nz7a2pp 2dx059p2t30q04 129fy3yj4n7pxb 10ffv6pfmr3bx2 1cu16dzgjyyysj 1dkfqxss9cwtg8 13xsyjsjbgz4yy 17jds61daks522 12tnt2pa3b9prp 166gkwjphn9zvb 1gv2fufczm34s4 1dcp9w720y3zfm 1bjyp0nj6j2nhj 10xa2mbckpp4xa 10zs65b6zpquxb 10dspay35dqdgt 1dwhb1gruxvx9p 127nktfu251hg8 15ddsan4jp3zap 1dnzpwyy0tw969 1fr26gt7zucnsp 103zktf63bd1bs 1已选择25行。
cqg6vfbga9q95这条sql在物理读也是居高榜首，那么他一定有问题
查看sql_id为cqg6vfbga9q95的SQL
SQL> select * from dba_hist_sqltext where sql_id='cqg6vfbga9q95';DBID SQL_IDSQL_TEXT COMMAND_TYPE---------- ------------- ------------------------------ ------------801114584 cqg6vfbga9q95 select xx from xx;3
异常处理
优先确认相关的SQL具体的功能，判断是否为系统异常操作
如果是异常操作导致，则忽略不计如果为系统正常操作导致，则判断是否需要新增TEMP表空间
