Oracle Tips and Techniques

Error:ORA-30926: 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:

On many websites you will see the action you need to take is
“Remove any non-deterministic where clauses and reissue the dml.”

This is not that clear – at least for me.

So here is an explanation with example:

The error occurs when the target table (where you want to merge) has a primary key/unique key
constraints and the source table (from where data to be merged into target) or source query
contains more than 1 record for the unique key.

In other words if you have multiple records in the source table that matches the unique key value
of one single record in the target table you will get this error.

See this example below:

create table t1(a number,  b number, constraint pk_t1 primary key(a));

create table t2(a number,  b number);

insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (3,1);
insert into t1 values (4,1); Continue reading →