重庆思庄Oracle、Redhat认证学习论坛

标题: 日志报错ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [打印本页]

作者: 刘泽宇 时间: 2021-6-13 09:22
标题: 日志报错ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
查看告警日志看到报错：
ORA-1652: unable to extend temp segment by 128 in tablespace                TEMP

第一步：检查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                +DATA/racdb/tempfile/temp02.dbf       10 NO
TEMP                +DATA/racdb/tempfile/temp03.dbf       10 NO
TEMP                +DATA/racdb/tempfile/temp01.dbf       10 NO
TEMP                +DATA/racdb/tempfile/temp04.dbf       10 NO

可以看出temp表空间下有4个数据文件，每个大小为10G，且未开启自动扩展
目前temp表空间总大小为40G
那么可知，数据库单次获取的数据量应该小于40G，否则就会出现temp表空间不足的情况
查询在异常时间内（2021-06-08 12:00至1:00），占用资源超过40G的SQL

生成指定时间段的AWR报告
告警日志的报错信息是12点至1点左右，所以awr报告生成时间从12点-1点

可以看到发生了等待事件direct path read 和direct path read temp

查看导致direct path write等待事件的sql
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 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表空间

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)