ORA-1652:临时表空间异常优化处理

1、查看 alert_PROD.log

【错误信息】：ORA-1652: unable to extend temp segment by 128 in tablespace TEMP1

查看临时表空间基础信息及其使用情况：

基础信息查看：

select dtf.TABLESPACE_NAME, dtf.file_name, dtf.BYTES / 1024 / 1024 as "MB"  from dba_temp_files dtf order by dtf.TABLESPACE_NAME;

使用情况查看：

SELECT d.tablespace_name "Name",       TO_CHAR(NVL(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.900') "Size (G)",       TO_CHAR(NVL(t.hwm, 0) / 1024 / 1024 / 1024, '99999999.999') "HWM (G)",       TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % ",       TO_CHAR(NVL(t.bytes / 1024 / 1024 / 1024, 0), '99999999.999') "Using (G)",       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"  FROM sys.dba_tablespaces d,       (select tablespace_name, sum(bytes) bytes          from dba_temp_files         group by tablespace_name) a,       (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes          from v$temp_extent_pool         group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+)   AND d.tablespace_name = t.tablespace_name(+)   AND d.extent_management like 'LOCAL'   AND d.contents like 'TEMPORARY';

通过查看临时表空间组中的表空间可以判断，该表空间处于异常状态。

2、启动ORA-1652跟踪错误

在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;

3、查看alert_PROD.log中错误日志

Errors in file /ora/prod/12.1.0/admin/PROD_erpdb/diag/rdbms/prod/PROD/trace/PROD_ora_5165.trc: more /ora/prod/12.1.0/admin/PROD_erpdb/diag/rdbms/prod/PROD/trace/PROD_ora_5165.trc

----- Error Stack Dump -----ORA-1652: unable to extend temp segment by 128 in tablespace TEMP1----- Current SQL Statement for this session (sql_id=cfrvgzfkpxp49) -----注：中间为需要优化的代码----- PL/SQL Stack -----

4、打印SQL执行计划

pl/sql Developer 工具按F5.

5、分析执行计划

全表扫描（Table access full）的子句。

通过SQL优化的规则分析该字句。

6、对该SQL重要部分在Test系统中进行优化测试。

（1）执行优化前：

（2）创建索引

create index ind_CPMD_TRANSACTION_ID on cux.CUX_PA_MATERIAL_A_DETAILS(nvl(CPMD.TRANSACTION_ID,0));

（3）修改后

7、检查Test系统中进行优化结果。

优化前后对比

8、在Prod系统中进行部署

（1）备份 CUX_PA_MAT_A_DETS_COLLECT_PKG到本地

（2）创建索引

create index ind_CPMD_TRANSACTION_ID on cux.CUX_PA_MATERIAL_A_DETAILS(nvl(CPMD.TRANSACTION_ID,0));

（3）进行优化，并添加注释

A、WHERE CPMD.TRANSACTION_ID is not null    替换为  WHERE NVL(CPMD.TRANSACTION_ID,0) >0  B、 AND MTLN.LOT_NUMBER NOT LIKE '%|%'   替换为：AND INSTR(MTLN.LOT_NUMBER,'|') < 1 C、 AND T.ACCOUNT_NAME LIKE '%出库%';     替换为：AND INSTR(T.ACCOUNT_NAME,'出库') > 0 ;

（4）进行代码检查

检查项：a、检查修改代码是否正确

b、检查源代码是否已经注释

C、检查是否在修改代码后添加注释（包括时间、功能、修改人员）

（5）执行代码，重新提交请求

9、执行查询操作，查看结果

整体优化前后对比：

9、检查alert_PROD.log中是否出现ORA-1652错误

Beginning log switch checkpoint up to RBA [0x8c9.2.10], SCN: 5965544065707Tue Mar 20 16:19:47 2018Thread 1 advanced to log sequence 2249 (LGWR switch)  Current log# 1 seq# 2249 mem# 0: /data/prod/proddata/log01a.dbf  Current log# 1 seq# 2249 mem# 1: /data/prod/proddata/log01b.dbfTue Mar 20 16:19:51 2018Archived Log entry 2248 added for thread 1 sequence 2248 ID 0x1556bb36 dest 1:Tue Mar 20 16:24:52 2018Completed checkpoint up to RBA [0x8c9.2.10], SCN: 5965544065707Tue Mar 20 16:27:28 2018Incremental checkpoint up to RBA [0x8c9.a420.0], current log tail at RBA [0x8c9.21670.0]Tue Mar 20 16:47:32 2018Incremental checkpoint up to RBA [0x8c9.32675.0], current log tail at RBA [0x8c9.3d64f.0]Tue Mar 20 16:58:03 2018Tue Mar 20 17:00:03 2018Closing scheduler windowClosing Resource Manager plan via scheduler windowClearing Resource Manager plan via parameterTue Mar 20 17:07:35 2018Incremental checkpoint up to RBA [0x8c9.547b9.0], current log tail at RBA [0x8c9.5979d.0]Tue Mar 20 17:27:37 2018Incremental checkpoint up to RBA [0x8c9.5eece.0], current log tail at RBA [0x8c9.609cf.0]

10、关闭ORA-1652跟踪事件。对应的关闭脚本如下：

   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 = '*';