ora-600[6002]解决

   开发在调试一个存储过程时，执行不成功，然后一步一步做测试，在做一条INSERT INTO PICTURETEMP SELECT * FROM PICTUREVIEW的sql语句时报错：

SQL> INSERT INTO PICTURETEMP SELECT * FROM PICTUREVIEW;
INSERT INTO PICTURETEMP SELECT * FROM PICTUREVIEW
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [6002], [32], [32], [2], [0], [], [], [], [], [], [], []

alert日志显示如下：

Fri Sep 18 13:04:19 2015
Errors in file /home/oracle/app/diag/rdbms/ecdb/ecdb/trace/ecdb_ora_22045.trc  (incident=40301):
ORA-00600: internal error code, arguments: [6002], [32], [32], [2], [0], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 18 13:04:22 2015
Sweep [inc][40301]: completed
Fri Sep 18 13:06:38 2015
Errors in file /home/oracle/app/diag/rdbms/ecdb/ecdb/trace/ecdb_ora_22045.trc  (incident=40355):
ORA-00600: internal error code, arguments: [6002], [32], [32], [2], [0], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 18 13:06:40 2015
Sweep [inc][40355]: completed
Fri Sep 18 13:13:00 2015
Thread 1 advanced to log sequence 27425 (LGWR switch)
  Current log# 2 seq# 27425 mem# 0: /newdisk/oradata/ecdb/redo02.log
Thread 1 advanced to log sequence 27426 (LGWR switch)
  Current log# 3 seq# 27426 mem# 0: /newdisk/oradata/ecdb/redo03.log
Fri Sep 18 13:13:07 2015
Errors in file /home/oracle/app/diag/rdbms/ecdb/ecdb/trace/ecdb_j001_23086.trc  (incident=36698):
ORA-00600: internal error code, arguments: [6002], [32], [32], [2], [0], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thread 1 advanced to log sequence 27427 (LGWR switch)
  Current log# 1 seq# 27427 mem# 0: /newdisk/oradata/ecdb/redo01.log

查看trace文件

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.32-431.el6.x86_64
Version:        #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: ecdb
Redo thread mounted by this instance: 1
Oracle process number: 81
Unix process pid: 23086, image: oracle@localhost.localdomain (J001)

*** 2015-09-18 13:13:07.338
*** SESSION ID:(430.18203) 2015-09-18 13:13:07.338
*** CLIENT ID:() 2015-09-18 13:13:07.338
*** SERVICE NAME:(SYS$USERS) 2015-09-18 13:13:07.338
*** MODULE NAME:(DBMS_SCHEDULER) 2015-09-18 13:13:07.338
*** ACTION NAME:(SOLR_TIME_EXECUTE_JOB) 2015-09-18 13:13:07.338


DDE: Problem Key 'ORA 600 [6002]' was flood controlled (0x6) (incident: 36698)
ORA-00600: internal error code, arguments: [6002], [32], [32], [2], [0], [], [], [], [], [], [], []

无有用信息。

  根据mos ORA-600 [6002] "Index block check" (文档 ID 47449.1)介绍

SUGGESTIONS:


  Check for problem indexes associated with any tables involved in 
  the failing SQL statement.


     ANALYZE TABLE <tablename> VALIDATE STRUCTURE CASCADE;


  Drop and recreate any indexes that are reported as being a problem.

上面的SQL语句中PICTUREVIEW是个视图，表PICTURETEMP上无索引，先把所涉及的表都先分析一遍。

SQL> ANALYZE TABLE PICTURETEMP VALIDATE STRUCTURE CASCADE;

Table analyzed.

重建几个表所涉及的索引

SQL> ALTER INDEX IDX_PICTURE_CODE REBUILD ONLINE;
Index altered.
SQL> ALTER INDEX IDX_PCM_PICTURE_GOODS_SID REBUILD ONLINE;
Index altered.
SQL> ALTER INDEX IDX_PCM_PICTURE_IS_PRIMARY REBUILD ONLINE;
Index altered.
SQL> ALTER INDEX PK_PCM_PICTURE REBUILD ONLINE; --此处必须加online参数
Index altered.

重建后再次执行上述语句，发现问题依旧。

于是先把picturetemp这张表导出，询问开发是否能把此表清空，说可以。那么就先truncate。

然后再做INSERT INTO PICTURETEMP SELECT * FROM PICTUREVIEW;无报错，执行成功。

总结：由于开发人员再做测试时，语句执行慢，查看状态总是显示同条信息，怀疑是存储过程有问题，于是在pl/sql里老是做停止操作，造成此表有逻辑坏块，清空再插入时正常。