在資料庫的alert檔案中發現了這個錯誤，這個bug似乎是由MERGE語句引起的。

解決ORA-600(16164)錯誤的過程（一）：http://yangtingkun.itpub.net/post/468/476593

這篇描述錯誤的解決過程。

上一篇介紹了錯誤資訊和metalink上的描述，下面嘗試解決或繞開這個錯誤。

如果要自己解決問題，首先看看問題能否重現：

SQL> MERGE /*+ append */ INTO
2    MIS2_USER U
3    USING (SELECT A.ID          USER_ID,
4                  A.CODE        CODE,
5                  A.ENABLE_FLAG USER_ENABLE_FLAG,
6                  B.ENABLE_FLAG ORG_ENABLE_FLAG,
7                  A.NAME        USER_NAME,
8                  B.NAME        ORG_NAME,
9                  C.DATA_ORG_ID ORG_ID,
10                  A.CREATE_DATE CREATE_DATE
11             FROM GPO_USR_USER A, GPO_USR_ORG B, GPO_REG_ORG C
12            WHERE A.ORG_ID = B.ID
13              AND B.REG_ORG_ID = C.ID
14              AND C.FACTORY_FLAG = '1'
15           UNION ALL
16           SELECT A.ID            USER_ID,
17                  A.USER_CODE     CODE,
18                  A.ENABLE_FLAG   USER_ENABLE_FLAG,
19                  B.ENABLE_FLAG   ORG_ENABLE_FLAG,
20                  A.USER_NAME     USER_NAME,
21                  B.NAME          ORG_NAME,
22                  A.DEFAULT_ORGID ORG_ID,
23                  A.CREATE_DATE   CREATE_DATE
24             FROM USR_USER A, CAT_ORG B
25            WHERE A.DEFAULT_ORGID = B.ID
26              AND B.ORG_TYPE = '1') B
27    ON (U.USER_ID = B.USER_ID)
28    WHEN MATCHED THEN
29      UPDATE
30         SET U.CODE             = B.CODE,
31             U.USER_ENABLE_FLAG = B.USER_ENABLE_FLAG,
32             U.ORG_ENABLE_FLAG = B.ORG_ENABLE_FLAG,
33             U.USER_NAME        = B.USER_NAME,
34             U.ORG_NAME         = B.ORG_NAME,
35             U.ORG_ID           = B.ORG_ID
36    WHEN NOT MATCHED THEN
37      INSERT
38        (USER_ID,
39         CODE,
40         USER_ENABLE_FLAG,
41         ORG_ENABLE_FLAG,
42         USER_NAME,
43         ORG_NAME,
44         ORG_ID,
45         CREATE_DATE)
46      VALUES
47        (B.USER_ID,
48         B.CODE,
49         B.USER_ENABLE_FLAG,
50         B.ORG_ENABLE_FLAG,
51         B.USER_NAME,
52         B.ORG_NAME,
53         B.ORG_ID,
54         B.CREATE_DATE)
55 ;
MIS2_USER U
*
ERROR at line 2:
ORA-00600: internal error code, arguments: [16164], [0], [], [], [], [], [], []

其實問題重新並不可怕，無法重現的問題才難以解決。

首先想到的是，問題是否和APPEND有關，因為其他部分沒有什麼特殊之處，因此先懷疑和直接路徑有關。

SQL> MERGE INTO
2    MIS2_USER U
3    USING (SELECT A.ID          USER_ID,
.
.
.
54         B.CREATE_DATE)
55 ;
MERGE INTO
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

錯誤果然和直接路徑相關，雖然這裡仍然出現錯誤，不過問題已經由一個ORA-600的內部錯誤，變成了ORA-30926這種常規錯誤了。那麼現在可以懷疑，導致問題的真正原因就是這個ORA-30926錯誤，而直接路徑則是將一個普通錯誤程式設計ORA-600的原因。當然，目前只是一個懷疑，還需要進一步去驗證。

下面來解決這個ORA-30926錯誤，先看看Oracle的錯誤引數手冊中的描述：

ORA-30926 unable to get a stable set of rows in the source tables
Cause: A stable set of rows could not be got because of a large amount of DML activity or a non-deterministic where clause.
Action: Remove any non-deterministic where clauses and reissue the DML.

問題似乎和DML的不確定性有關，不過無論是錯誤資訊還是給出的解決問題的描述，都不是十分明確，看完之後對於解決問題似乎幫助不大。

既然官方文件上找不到答案，不妨再到METALINK上看看ORA-30926的錯誤描述。在文件Doc ID: 471956.1中，可以看到這樣的描述：This error occurs with the Cost based Optimizer but not with RULE.

那麼嘗試新增RULE提示，看看能否解決問題：

SQL> MERGE /*+ RULE */ INTO
2    MIS2_USER U
3    USING (SELECT A.ID          USER_ID,
.
.
.
54         B.CREATE_DATE)
55 ;
MIS2_USER U
*
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables

雖然仍然出現錯誤，但是可以發現，報錯資訊的位置已經發生了變化。看來RULE確實起了一定的作用，不過最終仍然沒有辦法避免這個ORA-30926錯誤。

繼續檢視剛才的問題，可以發現，對於MERGE語句，還要保證USING ON語句的連線不出現重複的資料。

那麼現在最大的懷疑莫過於進行連線的兩張表是否存在重複記錄了，既然MIS2_USER是一張單表，那麼首先檢查這張表：

SQL> SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME
2 FROM USER_CONS_COLUMNS
3 WHERE CONSTRAINT_NAME =
4 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
5 WHERE TABLE_NAME = 'MIS2_USER'
6 AND CONSTRAINT_TYPE = 'P')
7 AND WNER = USER;

CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
PK_MIS2_USER MIS2_USER USER_ID

1 row selected.

SQL> SELECT COUNT(*), COUNT(DISTINCT USER_ID) FROM MIS2_USER;

COUNT(*) COUNT(DISTINCTUSER_ID)
---------- ----------------------
28268 28268

可以看到USER_ID是MIS2_USER的主鍵，而且從表中的資料可以看到，對於MIS2_USER表來說，資料沒有重複。

下面就是嫌疑最大的UNION ALL語句了，檢查一下資料是否重複：

SQL> SELECT COUNT(*), COUNT(DISTINCT USER_ID) FROM
2 (SELECT A.ID          USER_ID,
3                  A.CODE        CODE,
4                  A.ENABLE_FLAG USER_ENABLE_FLAG,
5                  B.ENABLE_FLAG ORG_ENABLE_FLAG,
6                  A.NAME        USER_NAME,
7                  B.NAME        ORG_NAME,
8                  C.DATA_ORG_ID ORG_ID,
9                  A.CREATE_DATE CREATE_DATE
10             FROM GPO_USR_USER A, GPO_USR_ORG B, GPO_REG_ORG C
11            WHERE A.ORG_ID = B.ID
12              AND B.REG_ORG_ID = C.ID
13              AND C.FACTORY_FLAG = '1'
14           UNION ALL
15           SELECT A.ID            USER_ID,
16                  A.USER_CODE     CODE,
17                  A.ENABLE_FLAG   USER_ENABLE_FLAG,
18                  B.ENABLE_FLAG   ORG_ENABLE_FLAG,
19                  A.USER_NAME     USER_NAME,
20                  B.NAME          ORG_NAME,
21                  A.DEFAULT_ORGID ORG_ID,
22                  A.CREATE_DATE   CREATE_DATE
23             FROM USR_USER A, CAT_ORG B
24            WHERE A.DEFAULT_ORGID = B.ID
25              AND B.ORG_TYPE = '1');

COUNT(*) COUNT(DISTINCTUSER_ID)
---------- ----------------------
29060 27125

果然出現了資料的重複，看來問題的根源找到了。

問題找到了，解決問題的方法也就找到了。只要確保UNION ALL兩部分產生的USER_ID不重複就可以了，簡單改寫一下SQL：

SQL> MERGE INTO
2    MIS2_USER U
3    USING (SELECT A.ID          USER_ID,
4                  A.CODE        CODE,
5                  A.ENABLE_FLAG USER_ENABLE_FLAG,
6                  B.ENABLE_FLAG ORG_ENABLE_FLAG,
7                  A.NAME        USER_NAME,
8                  B.NAME        ORG_NAME,
9                  C.DATA_ORG_ID ORG_ID,
10                  A.CREATE_DATE CREATE_DATE
11             FROM GPO_USR_USER A, GPO_USR_ORG B, GPO_REG_ORG C
12            WHERE A.ORG_ID = B.ID
13              AND B.REG_ORG_ID = C.ID
14              AND C.FACTORY_FLAG = '1'
15           UNION ALL
16           SELECT A.ID            USER_ID,
17                  A.USER_CODE     CODE,
18                  A.ENABLE_FLAG   USER_ENABLE_FLAG,
19                  B.ENABLE_FLAG   ORG_ENABLE_FLAG,
20                  A.USER_NAME     USER_NAME,
21                  B.NAME          ORG_NAME,
22                  A.DEFAULT_ORGID ORG_ID,
23                  A.CREATE_DATE   CREATE_DATE
24             FROM USR_USER A, CAT_ORG B
25            WHERE A.DEFAULT_ORGID = B.ID
26              AND B.ORG_TYPE = '1'
27              AND A.ID NOT IN
28                     (
29                             SELECT A.ID FROM GPO_USR_USER A, GPO_USR_ORG B, GPO_REG_ORG C
30                             WHERE A.ORG_ID = B.ID
31                             AND B.REG_ORG_ID = C.ID
32                             AND C.FACTORY_FLAG = '1'
33                     )
34             ) B
35    ON (U.USER_ID = B.USER_ID)
36    WHEN MATCHED THEN
37      UPDATE
38         SET U.CODE             = B.CODE,
39             U.USER_ENABLE_FLAG = B.USER_ENABLE_FLAG,
40             U.ORG_ENABLE_FLAG = B.ORG_ENABLE_FLAG,
41             U.USER_NAME        = B.USER_NAME,
42             U.ORG_NAME         = B.ORG_NAME,
43             U.ORG_ID           = B.ORG_ID
44    WHEN NOT MATCHED THEN
45      INSERT
46        (USER_ID,
47         CODE,
48         USER_ENABLE_FLAG,
49         ORG_ENABLE_FLAG,
50         USER_NAME,
51         ORG_NAME,
52         ORG_ID,
53         CREATE_DATE)
54      VALUES
55        (B.USER_ID,
56         B.CODE,
57         B.USER_ENABLE_FLAG,
58         B.ORG_ENABLE_FLAG,
59         B.USER_NAME,
60         B.ORG_NAME,
61         B.ORG_ID,
62         B.CREATE_DATE)
63 ;

27125 rows merged.

SQL> ROLLBACK;

Rollback complete.

問題得以解決。最後檢查一下直接路徑是否還存在錯誤：

SQL> MERGE /*+ APPEND */ INTO
2    MIS2_USER U
3    USING (SELECT A.ID          USER_ID,
.
.
.
62         B.CREATE_DATE)
63 ;

27125 rows merged.

SQL> ROLLBACK;

Rollback complete.

其實解決問題的過程就是不斷懷疑，不斷嘗試的過程。即使沒有metalink的幫助，解決問題的思路也是一樣的。

首先要懷疑的就是不經常使用的地方，比如這個例子中的APPEND。隨後比較特別的就是UNION ALL。然後需要懷疑就是一些容易導致問題的地方，比如連線列。

而這個例子中，問題就出現在上面3個地方。

有了懷疑的物件，就要透過不斷的嘗試來驗證，根據驗證的結果來確定懷疑或者排除懷疑。而整個解決問題的過程，無非是不斷重複上面這個過程而已。

其實嚴格意義上講，這樣並不算真正意義上解決了這個錯誤。真正意義上解決這個bug，要求能構造一個很簡單的例子來重現錯誤，比如對於這個問題，可以透過下面的例子在9204以前版本上重現錯誤：

SQL> CONN TEST/TEST@172.25.88.94/TESTDATA
已連線。
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> CREATE TABLE T_600_16164 (ID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> INSERT INTO T_600_16164 SELECT ROWNUM, TNAME FROM TAB;

已建立75行。

SQL> COMMIT;

提交完成。

SQL> MERGE INTO T_600_16164 A
2 USING (SELECT ROWNUM ID, TNAME NAME FROM TAB
3 UNION ALL
4 SELECT ROWNUM, TABLE_NAME FROM USER_TABLES) B
5 ON (A.ID = B.ID)
6 WHEN MATCHED THEN
7 UPDATE SET A.NAME = B.NAME
8 WHEN NOT MATCHED THEN
9 INSERT (ID, NAME) VALUES (B.ID, B.NAME);
MERGE INTO T_600_16164 A
*
第 1 行出現錯誤:
ORA-30926: 無法在源表中獲得一組穩定的行

SQL> MERGE /*+ APPEND */ INTO T_600_16164 A
2 USING (SELECT ROWNUM ID, TNAME NAME FROM TAB
3 UNION ALL
4 SELECT ROWNUM, TABLE_NAME FROM USER_TABLES) B
5 ON (A.ID = B.ID)
6 WHEN MATCHED THEN
7 UPDATE SET A.NAME = B.NAME
8 WHEN NOT MATCHED THEN
9 INSERT (ID, NAME) VALUES (B.ID, B.NAME);
MERGE /*+ APPEND */ INTO T_600_16164 A
*
第 1 行出現錯誤:
ORA-00600: 內部錯誤程式碼，引數: [16164], [0], [], [], [], [], [], []

直到這一步為止，這個問題才真正意義上被解決。

解決ORA-600(16164)錯誤的過程（二）

相關文章