ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE11G ORA-600[kkdcacr ptn_kxcp]错误

ORACLE11G ORA-600[kkdcacr ptn_kxcp]错误

原创 Linux操作系统 作者:yangtingkun 时间:2007-10-12 00:00:00 0 删除 编辑

用了11g很长时间,还是第一次碰到ORA-600错误。这个错误信息在以前的版本中还重来没有看到过。


总的来说Oracle11g的测试还是很到位的,用了这么长时间,测试了这么多的新功能,一共只碰到过一个7445错误和一个600错误。对于一个新推出的版本,已经算是很不错了。

这个错误可以重现,当执行下面的SQL,会产生这个错误:

SQL> SELECT B.OWNER, B.TABLE_NAME, OBJECT_NAME, SUBOBJECT_NAME
2 FROM
3 DBA_OBJECTS A,
4 (
5 SELECT B.OWNER, B.TABLE_NAME, DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
6 FROM T_PRIMARY A, T_FOREIGN B
7 WHERE A.OWNER = B.OWNER
8 AND A.TABLE_NAME = B.TABLE_NAME
9 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
10 ) B
11 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;
DBA_OBJECTS A,
*
3 行出现错误:
ORA-00600:
内部错误代码, 参数: [kkdcacr: ptn_kxcp], [], [], [], [], [], [], []

至于T_PRIMARYT_FOREIGN的创建脚本和含义,可以参考:http://yangtingkun.itpub.net/post/468/404694

通过EXPLAIN PLAN FOR查看执行计划也会报错:

SQL> EXPLAIN PLAN FOR
2 SELECT B.OWNER, B.TABLE_NAME, OBJECT_NAME, SUBOBJECT_NAME
3 FROM
4 DBA_OBJECTS A,
5 (
6 SELECT B.OWNER, B.TABLE_NAME, DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
7 FROM T_PRIMARY A, T_FOREIGN B
8 WHERE A.OWNER = B.OWNER
9 AND A.TABLE_NAME = B.TABLE_NAME
10 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
11 ) B
12 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;
DBA_OBJECTS A,
*
4 行出现错误:
ORA-00600:
内部错误代码, 参数: [kkdcacr: ptn_kxcp], [], [], [], [], [], [], []

去掉内存查询中的连接,只保留T_FOREIGN表,则错误消失。

SQL> SELECT B.OWNER, B.TABLE_NAME, OBJECT_NAME, SUBOBJECT_NAME
2 FROM
3 DBA_OBJECTS A,
4 (
5 SELECT B.OWNER, B.TABLE_NAME, DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
6 FROM T_FOREIGN B
7 WHERE TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
8 ) B
9 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;

OWNER TABLE_NAME OBJECT_NAM SUBOBJECT_NAME
-------- ---------- ---------- ------------------------------
YANGTK T_PRIMARY T_FOREIGN
P4 CTXSYS DR$INDEX T_FOREIGN P3
YANGTK T T_FOREIGN P2
SYS DUAL T_FOREIGN P1

在子查询中包含T_PRIMARY的字段,并在外层查询中也包括T_PRIMARY的字段,则错误消失:

SQL> SELECT B.OWNER, B.TABLE_NAME, TABLESPACE_NAME, OBJECT_NAME, SUBOBJECT_NAME
2 FROM
3 DBA_OBJECTS A,
4 (
5 SELECT B.OWNER, B.TABLE_NAME, A.TABLESPACE_NAME,
6 DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
7 FROM T_PRIMARY A, T_FOREIGN B
8 WHERE A.OWNER = B.OWNER
9 AND A.TABLE_NAME = B.TABLE_NAME
10 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
11 ) B
12 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;

OWNER TABLE_NAME TABLESPACE_NAME OBJECT_NAM SUBOBJECT_NAME
-------- ---------- ---------------- ---------- ------------------------------
YANGTK T_PRIMARY T_FOREIGN
P4 CTXSYS DR$INDEX SYSAUX T_FOREIGN P3
YANGTK T YANGTK T_FOREIGN P2
SYS DUAL SYSTEM T_FOREIGN P1

甚至只需要在报错SQL的内容查询中加上ROWNUM伪列,就会确保查询不会出错:

SQL> SELECT B.OWNER, B.TABLE_NAME, OBJECT_NAME, SUBOBJECT_NAME
2 FROM
3 DBA_OBJECTS A,
4 (
5 SELECT B.OWNER, B.TABLE_NAME, DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID, ROWNUM
6 FROM T_PRIMARY A, T_FOREIGN B
7 WHERE A.OWNER = B.OWNER
8 AND A.TABLE_NAME = B.TABLE_NAME
9 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
10 ) B
11 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;

OWNER TABLE_NAME OBJECT_NAM SUBOBJECT_NAME
-------- ---------- ---------- ------------------------------
YANGTK T_PRIMARY T_FOREIGN
P4 CTXSYS DR$INDEX T_FOREIGN P3
YANGTK T T_FOREIGN P2
SYS DUAL T_FOREIGN P1

怀疑是由于DBA_OBJECTS视图在查询中被MERGE造成的,只需要确保AB先进行连接,就不会报错。

SQL> SELECT /*+ USE_HASH(A B) */ B.OWNER, B.TABLE_NAME, OBJECT_NAME, SUBOBJECT_NAME
2 FROM
3 DBA_OBJECTS A,
4 (
5 SELECT B.OWNER, B.TABLE_NAME, DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
6 FROM T_PRIMARY A, T_FOREIGN B
7 WHERE A.OWNER = B.OWNER
8 AND A.TABLE_NAME = B.TABLE_NAME
9 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
10 ) B
11 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;

OWNER TABLE_NAME OBJECT_NAM SUBOBJECT_NAME
-------- ---------- ---------- ------------------------------
YANGTK T_PRIMARY T_FOREIGN
P4 CTXSYS DR$INDEX T_FOREIGN P3
YANGTK T T_FOREIGN P2
SYS DUAL T_FOREIGN P1

SQL> SELECT /*+ USE_MERGE(A B) */ B.OWNER, B.TABLE_NAME, OBJECT_NAME, SUBOBJECT_NAME
2 FROM
3 DBA_OBJECTS A,
4 (
5 SELECT B.OWNER, B.TABLE_NAME, DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
6 FROM T_PRIMARY A, T_FOREIGN B
7 WHERE A.OWNER = B.OWNER
8 AND A.TABLE_NAME = B.TABLE_NAME
9 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
10 ) B
11 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;

OWNER TABLE_NAME OBJECT_NAM SUBOBJECT_NAME
-------- ---------- ---------- ------------------------------
YANGTK T_PRIMARY T_FOREIGN
P4 CTXSYS DR$INDEX T_FOREIGN P3
YANGTK T T_FOREIGN P2
SYS DUAL T_FOREIGN P1

SQL> SELECT /*+ USE_NL(A B) */ B.OWNER, B.TABLE_NAME, OBJECT_NAME, SUBOBJECT_NAME
2 FROM
3 DBA_OBJECTS A,
4 (
5 SELECT B.OWNER, B.TABLE_NAME, DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
6 FROM T_PRIMARY A, T_FOREIGN B
7 WHERE A.OWNER = B.OWNER
8 AND A.TABLE_NAME = B.TABLE_NAME
9 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
10 ) B
11 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;
DBA_OBJECTS A,
*
3 行出现错误:
ORA-00600:
内部错误代码, 参数: [kkdcacr: ptn_kxcp], [], [], [], [], [], [], []

对于MERGE JOINHASH JOINDBA_OBJECTS都被当作一个整体,不会被MERGE,而采用NESTED LOOP,则可能导致视图被MERGE

建立一张新表,不使用REFERENCE分区,发现同样的SQL错误消失,看来这个错误和参考分区也有一定的关系:

SQL> CREATE TABLE T1 AS SELECT * FROM T_FOREIGN;

表已创建。

SQL> ALTER TABLE T1 ADD CONSTRAINTS FK_T1 FOREIGN KEY (OWNER, TABLE_NAME)
2 REFERENCES T_PRIMARY (OWNER, TABLE_NAME);

表已更改。

SQL> SELECT B.OWNER, B.TABLE_NAME, OBJECT_NAME, SUBOBJECT_NAME
2 FROM
3 DBA_OBJECTS A,
4 (
5 SELECT B.OWNER, B.TABLE_NAME, DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
6 FROM T_PRIMARY A, T1 B
7 WHERE A.OWNER = B.OWNER
8 AND A.TABLE_NAME = B.TABLE_NAME
9 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
10 ) B
11 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;

OWNER TABLE_NAME OBJECT_NAM SUBOBJECT_NAME
-------- ---------- ---------- ------------------------------
YANGTK T_PRIMARY
T1 CTXSYS DR$INDEX T1
YANGTK T T1
SYS DUAL T1

查询了一下METALINK,只发现一个同样的错误,根据描述问题确实是由于参考分区造成的,详细描述参考Oraclemetalink信息:Bug No6429206

Oracle给出了临时的解决方法,设置隐藏参数_optimizer_join_elimination_enabledFALSE

SQL> ALTER SESSION SET "_optimizer_join_elimination_enabled" = FALSE;

会话已更改。

SQL> SELECT B.OWNER, B.TABLE_NAME, OBJECT_NAME, SUBOBJECT_NAME
2 FROM
3 DBA_OBJECTS A,
4 (
5 SELECT B.OWNER, B.TABLE_NAME, DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
6 FROM T_PRIMARY A, T_FOREIGN B
7 WHERE A.OWNER = B.OWNER
8 AND A.TABLE_NAME = B.TABLE_NAME
9 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
10 ) B
11 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;

OWNER TABLE_NAME OBJECT_NAM SUBOBJECT_NAME
-------- ---------- ---------- ------------------------------
YANGTK T_PRIMARY T_FOREIGN
P4 CTXSYS DR$INDEX T_FOREIGN P3
YANGTK T T_FOREIGN P2
SYS DUAL T_FOREIGN P1

Oracle计划在11.1.0.7中解决这个bug

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69455/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10365994