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。