Producing ORA-08006 with MERGE and ROW MOVEMENT

Here is what the Oracle Docs say about ORA-08006:

ORA-08006: specified row no longer exists

Cause: the row has been deleted by another user since the operation began

Action: re-try the operation;

But I was “successfull” in finding a way to produce the error being alone on the system. This happens when the following parts cooperate:

    – partitioned table with enabled row movement

    – update on the partitioning key (causing row movement)

    – update is actually done via merge and tries to update the same row twice

Here is an example which is reproducable on 10.2.0.4.0:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

14:01:20 SQL> set tim off timi off
SQL> drop table tbl;

Table dropped.

SQL>
SQL> create table tbl (n number primary key, n_join number)
  2  partition by list (n)
  3  (partition part1 values(1),
  4   partition part2 values(2)
  5  )
  6  enable row movement;

Table created.

SQL>
SQL> insert into tbl (n, n_join) values (1, 1);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> merge into tbl partition (part1)
  2  using (select 1 as i, 2 as i_upd from dual
  3         union all
  4         select 1 as i, 2 as i_upd from dual
  5        ) tbl_upd
  6  on (tbl.n_join = tbl_upd.i)
  7  when matched then
  8  update set n=i_upd;
merge into tbl partition (part1)
           *
ERROR at line 1:
ORA-08006: specified row no longer exists

SQL> /
merge into tbl partition (part1)
           *
ERROR at line 1:
ORA-08006: specified row no longer exists

SQL> /
merge into tbl partition (part1)
           *
ERROR at line 1:
ORA-08006: specified row no longer exists

SQL>

As you can see I re-tried the operation twice (as Oracle Docs advise) to make sure it was not a temporary effect.

About these ads

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s