(由于微信板式限制,建议大家复制链接至浏览器查看效果更佳)
这个错误是在数据库的alert文件中发现的,导致问题的原因是MERGE语句。在alert文件中,错误信息如下:
Errors in file /u1/oracle/admin/repdb01/bdump/repdb01_j000_20925.trc:
ORA-00600: internal error code, arguments: [16164], [0], [], [], [], [], [], []
在对应的trace文件中,错误信息为:
ksedmp: internal or fatalerror
ORA-00600: internal error code, arguments: [16164], [0], [], [], [], [], [], []
Current SQL statementfor this session:
MERGE /*+ append */ INTO
MIS2_USER U
USING (SELECT A.ID USER_ID, A.CODE CODE, A.ENABLE_FLAG USER_ENABLE_FLAG,
B.ENABLE_FLAG ORG_ENABLE_FLAG,A.NAME USER_NAME, B.NAME ORG_NAME,
C.DATA_ORG_ID ORG_ID, A.CREATE_DATE CREATE_DATE
FROM GPO_USR_USER A, GPO_USR_ORG B, GPO_REG_ORGC
WHERE A.ORG_ID = B.ID AND B.REG_ORG_ID = C.ID AND C.FACTORY_FLAG = '1'
UNION ALL
SELECT A.ID USER_ID, A.USER_CODE CODE, A.ENABLE_FLAG USER_ENABLE_FLAG,
B.ENABLE_FLAG ORG_ENABLE_FLAG,A.USER_NAME USER_NAME, B.NAME ORG_NAME,
A.DEFAULT_ORGID ORG_ID, A.CREATE_DATE CREATE_DATE
FROM USR_USER A, CAT_ORG B
WHERE A.DEFAULT_ORGID = B.ID AND B.ORG_TYPE = '1') B
ON (U.USER_ID = B.USER_ID)
WHEN MATCHED THEN
UPDATE
SET U.CODE =B.CODE,
U.USER_ENABLE_FLAG = B.USER_ENABLE_FLAG,
U.ORG_ENABLE_FLAG =B.ORG_ENABLE_FLAG,
U.USER_NAME =B.USER_NAME,
U.ORG_NAME =B.ORG_NAME,
U.ORG_ID =B.ORG_ID
WHEN NOT MATCHED THEN
INSERT (USER_ID, CODE, USER_ENABLE_FLAG, ORG_ENABLE_FLAG,
USER_NAME, ORG_NAME, ORG_ID, CREATE_DATE)
VALUES (B.USER_ID, B.CODE, B.USER_ENABLE_FLAG, B.ORG_ENABLE_FLAG,
B.USER_NAME, B.ORG_NAME, B.ORG_ID, B.CREATE_DATE)
----- PL/SQL Call Stack-----
object line object
handle number name
6b1cea980 474 procedure MIS2.P_INIT_DATA
6a33fd260 1 anonymous block
----- Call Stack Trace-----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+328 CALL ksedst()+0 FFFFFFFF7FFF5350?
000000000 ? 000000000 ?
00000003E ?
FFFFFFFF7FFF5BE8 ?
1031D56C8 ?
kgeriv()+208 PTR_CALL 0000000000000000 000000000? 000103400 ?
0001035D9 ? 000102C00 ?
1035D9000 ? 1035D9C28 ?
kgeasi()+180 CALL kgeriv()+0 1035D9E88? 1036C7148 ?
错误SQL是一条MERGE语句,而查询Metalink的结果也发现,这个Bug确实和MERGE有关。在Metalink文档Doc ID:2754863.8中描述了这个错误。不过Oracle并没有对这个错误进行详细的描述,在这个描述文档对应的Bug文档Bug No. 2754863中,也只是给出了一个引发这个Bug的例子,并没有详细说明导致这个错误的原因,而且给出的例子十分复杂:SQL使用了CURSOR加表函数的方式获取用来关联的数据源,在获取内部查询数据的时候还指定了ROWNUM。由于Oracle给出的例子过于复杂,很多因素都可能引发这个错误,所以无法进一步判断导致问题的原因。综上所述没有办法指望Metalink中的信息来解决问题了。
虽然升级到新版本或者打上最新的patch能解决这个问题,但是对于当前解决这个问题的帮助并不大。因为无论是升级还是打patch都要进行大量的测试,安排维护停机时间等。为了解决这个小问题而升级系统显然是小题大做了。那么有否其他的办法能解决或者避开这个错误呢?
如果要解决问题,首先要重现问题。
重现代码如下:
SQL> MERGE /*+ append*/ INTO
2 MIS2_USERU
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 WHENMATCHED 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 WHENNOT 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], [], [], [], [], [], []
其实能重现的问题并不可怕,无法重现的问题才更棘手。
看到这个SQL,首先想到的是问题是否与APPEND提示有关。因为SQL的其他部分没有什么特殊之处,所以最先怀疑的是问题与直接路径有关。
测试代码如下:
SQL>MERGE INTO
2 MIS2_USERU
3 USING(SELECT A.ID USER_ID,
.
.
.
54 B.CREATE_DATE)
55 ;
MERGEINTO
*
ERRORat line 1:
ORA-30926: unable to geta stable set of rows in the source tables
错误果然与直接路径相关,虽然这次执行仍然出现错误,不过错误已经由一个ORA-600的内部错误变成了ORA-30926这种常规错误了。根据错误信息的变化分析,导致问题的真正原因可能就是这个ORA-30926的错误,而直接路径则是将一个普通的Oracle错误升级成了ORA-600的原因。当然,这还仅仅是推测,还须进一步验证。
首先来解决这个ORA-30926错误,看看Oracle的错误文档中的描述:
ORA-30926 unable to geta 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 activityor 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_USERU
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 rowsin the source tables
问题仍然出现,但是如果仔细观察可以发现,报错信息的位置已经发生了变化。不加RULE提示时问题出现在第1行,而加上RULE提示错误出现在第2行。看来RULE确实起了一定的作用,不过最终仍然没有避免这个ORA-30926错误。
根据以前使用MERGE的经验,对于MERGE语句而言,要保证USING ON连接语句没有重复的数据。会不会是违反了这个条件而导致错误产生呢?
现在怀疑进行连接的两张表是否存在重复记录,既然MIS2_USER是一张单表,那么首先检查这张表:
SQL> SELECT CONSTRAINT_NAME,TABLE_NAME, COLUMN_NAME
2 FROMUSER_CONS_COLUMNS WHERE CONSTRAINT_NAME =
3 (SELECTCONSTRAINT_NAME FROM USER_CONSTRAINTS
4 WHERETABLE_NAME = 'MIS2_USER' AND CONSTRAINT_TYPE = 'P') AND OWNER = 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 (SELECTA.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_USERU
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_USERA, 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 WHENMATCHED 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 WHENNOT 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_USERU
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 * FROMV$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise EditionRelease 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: Version9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0- Production
SQL> CREATE TABLET_600_16164 (ID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> INSERT INTO T_600_16164SELECT 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], [], [], [], [], [], []
可以通过自己搭建的环境来重现问题,说明对问题产生的原因已经清楚了。直到这一步,这个问题才在真正意义上被完全解决。
本篇摘取自《Oracle DBA手记》第03篇 诊断案例篇 - ORA系列错误与诊断(作者:杨廷琨),点击原文链接可下载《Oracle DBA手记》第01篇 DBA工作手记
配图来源:http://www.ora00600.com/wordpress/scripts/sqlnet/ora-3136/