ORACLE ORA-1652的解决方法-白红宇

前言：在检查数据库的alert日志，发现数据库报了ORA-1652: unable to extend temp segment的错误，以下记录的是整个处理过程：

1、检查当前数据库的表空间的大小，脚本如下

select file_name,file_id,bytes/1024/1024,status,autoextensible TABLESPACE_NAME from DBA_TEMP_FILES;

2、当前数据库的temp表空间已经设置32GB了，一般情况下如果临时表空间在20GB左右就够了（需要根据数据库的表数量级作为判断标准）

根据对系统的了解，初步判断这个增长明显是异常行为；

（如果表空间太小的话，可以通过语句增加：ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 10240M AUTOEXTEND OFF;）

3、因为每次报错都是在凌晨，所以不可能不睡觉一直跟踪临时表空间的使用情况，还好可以使用Oracle诊断事件跟踪ORA-1652事件,该诊断事件对系统的性能影响很小，因为只有在发生这个错误的时候，系统才会写入信息到alert日志中；

跟踪的级别分为三个等级：

在session级别启用数据库的跟踪：ALTER SESSION SET EVENTS '1652 trace name errorstack';

在系统级别启用数据库的跟踪：ALTER SYSTEM SET EVENTS '1652 trace name errorstack';

把该参数写入到spfile文件中： ALTER SYSTEM SET EVENT = '1652 trace name errorstack' SCOPE = SPFILE;
（关于参数的详细设置，也整理了一个文档，可以在博客中搜索）

对应的关闭脚本如下：

ALTER SESSION SET EVENTS '1652 trace name context off';

ALTER SYSTEM SET EVENTS '1652 trace name context off';

ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID = '*';

4、第二天的时候，果然又报错了，详细的报错信息如下：

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

Errors in file '/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/orcl_m000_15204728.trc:

打开相应的报错信息如下：

*** 2015-02-04 00:12:07.836

*** SESSION ID:(556.17195) 2015-02-04 00:12:07.836

*** CLIENT ID:() 2015-02-04 00:12:07.836

*** SERVICE NAME:(SYS$BACKGROUND) 2015-02-04 00:12:07.836

*** MODULE NAME:(MMON_SLAVE) 2015-02-04 00:12:07.836

*** ACTION NAME:(0000010 FINISHED190) 2015-02-04 00:12:07.836

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)

----- Error Stack Dump -----

ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP

----- Current SQL Statement for this session (sql_id=2d1p0p5k3f8fu) -----

select p, NULL, NULL from (select count(*) p from v$rman_status where operation = 'BLOCK MEDIA RECOVERY')

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

object line object

经过以上跟踪，终于知道了导致临时表空间耗光的罪魁祸首的原因，接下来是对语句进行分析；

5、另外需要注意的是，以上的跟踪出来的情况并不一定是主要原因，可能是压垮骆驼的最后一根稻草，因为前面有一条语句用了95%的TEMP表空间，紧接着又有一条语句用了6%的TEMP表空间，这个时候系统会记录第二条语句。但是真正的原因是前面那条使用了95%TEMP的表空间的语句；

在正常的情况下，可以监控表空间里面的内容的占用情况，也能分析出问题的原因，脚本如下：

SELECT S.sid || ',' || S.serial# sid_serial,

S.username,

T.blocks * TBS.block_size / 1024 / 1024 mb_used,

T.tablespace,

T.sqladdr address,

Q.hash_value,

Q.sql_text

FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = Q.address(+)

AND T.tablespace = TBS.tablespace_name

ORDER BY S.sid;

可以发现SID/SERIAL为5/1273的SESSION消耗了大量的temp表空间；

6、以上提供了整个问题的解决方法，建议大家动手试下，实验的步骤如下：

a、创建一个大表；

b、进行索引的创建；

c、诊断事件跟踪ORA-1652事件的开启；

d、再次创建索引；

e、检查报警日志；

总结：学习就是不断实验和总结的一个过程，每次动手解决记录问题的过程总是乐趣无穷；

......................................................................................................................................................................………………………………………

本文作者：JOHN，某上市公司DBA，业余时间专注于数据库的技术管理，从管理的角度去运用技术。