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: 5965544065707
Tue Mar 20 16:19:47 2018
Thread 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.dbf
Tue Mar 20 16:19:51 2018
Archived Log entry 2248 added for thread 1 sequence 2248 ID 0x1556bb36 dest 1:
Tue Mar 20 16:24:52 2018
Completed checkpoint up to RBA [0x8c9.2.10], SCN: 5965544065707
Tue Mar 20 16:27:28 2018
Incremental checkpoint up to RBA [0x8c9.a420.0], current log tail at RBA [0x8c9.21670.0]
Tue Mar 20 16:47:32 2018
Incremental checkpoint up to RBA [0x8c9.32675.0], current log tail at RBA [0x8c9.3d64f.0]
Tue Mar 20 16:58:03 2018
Tue Mar 20 17:00:03 2018
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Tue Mar 20 17:07:35 2018
Incremental checkpoint up to RBA [0x8c9.547b9.0], current log tail at RBA [0x8c9.5979d.0]
Tue Mar 20 17:27:37 2018
Incremental 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 = '*';