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:

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

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

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，业余时间专注于数据库的技术管理，从管理的角度去运用技术。

ORACLE技术博客：ORACLE 猎人笔记               数据库技术群：367875324 （请备注ORACLE管理 ） 

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