开发在调试一个存储过程时,执行不成功,然后一步一步做测试,在做一条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里老是做停止操作,造成此表有逻辑坏块,清空再插入时正常。