ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 解决ORA-600(16164)错误的过程(二)

解决ORA-600(16164)错误的过程(二)

原创 Linux操作系统 作者:yangtingkun 时间:2009-01-05 23:50:49 0 删除 编辑

在数据库的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_IDMIS2_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], [], [], [], [], [], []

直到这一步为止,这个问题才真正意义上被解决。

 

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

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

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10525191