MERGE and ORA-8006

I’m sure there will be a slew of post-Kscope wrap up posts coming out into the blogosphere, so in lieu of that, and the the fact that I’m just stuck in an airport waiting for a flight, I’ll offer something slightly more technical. I did a post a while back about a curious error “unable to get a stable set of rows” when using MERGE. Here is another variant which can occur when you allow rows to physically move during a MERGE.

How is that possible?” I hear you ask. Easy. All we need is partitioned table with ENABLE ROW MOVEMENT.



SQL> create table t (pk number primary key, x number)
  2      partition by list (pk)
  3      (partition p1 values(1),
  4       partition p2 values(2)
  5      )
  6  enable row movement;

Table created.

SQL>
SQL> insert into t values (1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> merge into t
  2  using (select 1 idx, 2 new_val from dual
  3         connect by level <= 2
  4        ) u
  5  on (t.x = u.idx)
  6  when matched then
  7    update set pk=new_val;
merge into t
           *
ERROR at line 1:
ORA-08006: specified row no longer exists

You can see that the merge is going to first change the PK column values from 1 to 2, which will move the row from one partition to another. The second row from the source (CONNECT BY LEVEL <= 2) will go hunting for that original value of 1 in its original partition and not find it there. Hence it “no longer exists”.

This is just another example of why you might want to consider cleansing input data for sensibility before aimlessly throwing it at a MERGE command.

One Comment on “MERGE and ORA-8006

  1. Problem isn’t in MERGE, that’s in used cursor u:
    using (select 1 idx, 2 new_val from dual
    connect by level <= 2
    ) u
    You will get another error ORA-30926 "unable to get a stable set of rows in the source tables" withless partitioning of table t

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: