ora-600[6002]解决

本文讨论了在使用Oracle数据库时遇到的SQL语句执行失败问题,通过逐步测试和排查,最终发现是由于存储过程的问题导致存储表出现逻辑坏块,通过清空并重新插入数据解决了问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值