ORACLE11G ORA-600[kkdcacr ptn_kxcp]错误

用了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_PRIMARY和T_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造成的，只需要确保A和B先进行连接，就不会报错。

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 JOIN和HASH JOIN，DBA_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，只发现一个同样的错误，根据描述问题确实是由于参考分区造成的，详细描述参考Oracle的metalink信息：Bug No：6429206。

Oracle给出了临时的解决方法，设置隐藏参数_optimizer_join_elimination_enabled为FALSE：

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-69455/