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.