ORA-1652: unable to extend temp segment by 128 in tablespace TEMP解决

问题描述

查看告警日志看到报错：

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.966507821          10 NO
TEMP                   +RACDB_DATA/racdb/tempfile/temp.438.971462603          10 NO
TEMP                   +RACDB_DATA/racdb/tempfile/temp01.dbf              10 NO
TEMP                   +RACDB_DATA/racdb/tempfile/temp.360.966507817          10 NO

可以看出temp表空间下有4个数据文件，每个大小为10G，且为开启自动扩展
目前temp表空间总大小为40G

• 错误定位
  根据配置可知，数据库单次获取的数据量应该小于40G，否则会出现temp表空间不足的情况
  查询在异常时间内（2020-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)  diff
from dba_hist_active_sess_history
where
SAMPLE_TIME between TO_TIMESTAMP ('2020-06-23 10:00:00', 'YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP ('2020-06-23 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
and TEMP_SPACE_ALLOCATED >=42949672960
order 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_history
where event = 'direct path write'
group by sql_id  order by count(*) desc;  2    3  

SQL_ID        COUNT(*)
------------- ----------
1gz69fj5z6kr6          47
1hvdu8dztxpfx          41
1s5dj6fckb73s          16
bt08vmahnwsm3           8
fp700pqw3yy00           5
b97wpwpmcuuvz           5
4wu7fmh7wcqqx           5
brntjyy188uk7           4
9g2rwr603usa7           4
0jfyv1q7m378w           3
2pxux7fu9tg7r           3
7spktz0yr1gxa           2
gayvdbrghw368           2
6fzzh1nqjc4t4           2
5f4k63fgy7t1r           2
               2
8u5159x70k02j           1
dmhqjx16c6w8y           1
956mwc1vc2ky6           1
9ycqu1mdf7svy           1
bqpnkpzk4bt2y           1
4k46mt51n7n8f           1
1arqnr7put41x           1
cmvrcbkpc2px3           1
5bzr32d7rya4f           1
47scxjyac9u03           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_history
where event = 'direct path write temp'
group by sql_id  order by count(*) desc;  2    3  

SQL_ID        COUNT(*)
------------- ----------
cqg6vfbga9q95          12
0h95w07ddcg2u           5
a9q2hvagvvs81           3
dc9g07nz7a2pp           2
dx059p2t30q04           1
29fy3yj4n7pxb           1
0ffv6pfmr3bx2           1
cu16dzgjyyysj           1
dkfqxss9cwtg8           1
3xsyjsjbgz4yy           1
7jds61daks522           1
2tnt2pa3b9prp           1
66gkwjphn9zvb           1
gv2fufczm34s4           1
dcp9w720y3zfm           1
bjyp0nj6j2nhj           1
0xa2mbckpp4xa           1
0zs65b6zpquxb           1
0dspay35dqdgt           1
dwhb1gruxvx9p           1
27nktfu251hg8           1
5ddsan4jp3zap           1
dnzpwyy0tw969           1
fr26gt7zucnsp           1
03zktf63bd1bs           1

已选择25行。

cqg6vfbga9q95这条sql在物理读也是居高榜首，那么他一定有问题

查看sql_id为cqg6vfbga9q95的SQL

SQL> select * from dba_hist_sqltext where sql_id='cqg6vfbga9q95';

      DBID SQL_ID     SQL_TEXT                           COMMAND_TYPE
---------- ------------- ------------------------------ ------------
801114584 cqg6vfbga9q95 select xx from xx;                3

异常处理

优先确认相关的SQL具体的功能，判断是否为系统异常操作

• 如果是异常操作导致，则忽略不计
• 如果为系统正常操作导致，则判断是否需要新增TEMP表空间