30926错误模拟（merge into bug导致）

SQL> conn xta7/xta7
已连接。
SQL> create table test1(id number,name varchar2(10));

表已创建。

SQL> insert into test1 values(1,'jack');

已创建 1 行。

SQL> insert into test1 values(2,'kate');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from test1;

        ID NAME
---------- --------------------
         1 jack
         2 kate

SQL> create table test2(id number,des varchar2(100));

表已创建。

SQL> merge into test2 b using (select id,name from test1) a
  2  on (a.id = b.id)
  3  when matched then
  4  update set b.des = a.name||'des'
  5  when not matched then
  6  insert (id,des) values(a.id,a.name);

2 行已合并。

SQL> select * from test2;

        ID    DES
---------------
        1  jack
        2  kate
SQL> set linesize 50
SQL> select * from test2;
     ID    DES
---------------
     1     jack
     2     kate
SQL> ed
已写入文件 afiedt.buf

  1* select * from test2
SQL> merge into test2 b using (select id,name from test1) a
  2  on (a.id = b.id)
  3  when matched then
  4  update set b.des = a.name||'des'
  5  when not matched then
  6  insert (id,des) values(a.id,a.name);

2 行已合并。

SQL> select * from test2;
     ID    DES
---------------
     1     jackdes
     2     katedes

SQL> set linesize 300
SQL> select * from test2;

        ID DES
     --- ----------------
         1 jackdes
         2 katedes

SQL> insert into test2 values(2,'kate');

已创建 1 行。

SQL> commit;

提交完成。

SQL> merge into test2 b using (select id,name from test1) a
  2  on (a.id = b.id)
  3  when matched then
  4  update set b.des = a.name||'des'
  5  when not matched then
  6  insert (id,des) values(a.id,a.name);

3 行已合并。

SQL> select * from test2;

        ID DES
---------- --------------
         1 jackdes
         2 katedes
         2 katedes

SQL> select * from test1;

        ID NAME
---------- --------------------
         1 jack
         2 kate

SQL> insert into test1 values(1,'jump');

已创建 1 行。

SQL> commit;

提交完成。

SQL> merge into test2 b using (select id,name from test1) a
  2  on (a.id = b.id)
  3  when matched then
  4  update set b.des = a.name||'des'
  5  when not matched then
  6  insert (id,des) values(a.id,a.name);
merge into test2 b using (select id,name from test1) a
*
ERROR 位于第 1 行:
ORA-30926: ??????????????

从上面的测试可以发现出现30926的错误的原因是因为test2中条件匹配到了多条在test1中的记录，因此在更新test2表的时候出现错误。

经过检查oerr ora 30926错误信息为
30926, 00000, "unable to get a stable set of rows in the source tables"
// *Cause:  A stable set of rows could not be got because of large dml
//          activity or a non-deterministic where clause.
// *Action: Remove any non-deterministic where clauses and reissue the dml.

跟踪该错误
会话跟踪：alter session set events '30296 trace name errorstack level 10';
系统范围内跟踪： alter system set event = '30296 trace name errorstack level 10' scope=spfile;重新启动数据库；
执行上述
merge into test2 b using (select id,name from test1) a
on (a.id = b.id)
when matched then
update set b.des = a.name||'des'
when not matched then
insert (id,des) values(a.id,a.name);
到user_dump_dest目录下寻找跟踪文件后，进行分析。