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);

insert into t2 values (1,5);
insert into t2 values (1,6);
insert into t2 values (2,7);
insert into t2 values (3,8);
insert into t2 values (5,9);

MERGE INTO t1 tt1
USING (SELECT a, b from t2) tt2
ON (tt1.a = tt2.a)
WHEN MATCHED THEN
UPDATE SET tt1.b = tt2.b
WHEN NOT MATCHED THEN
INSERT (a, b)
VALUES (tt2.a, tt2.b)
/

If you run the statements above, it will return the error ORA-30926.

Reason: It you ORA-30926 because your target table t1 has a primary key (hence unique key)
constraint on column a. Now look at the source table t2. T2 does not have any constraint and
it has two records where column a value is 1;

So if you select with

select * from t2 where a=1

then you get back two recors.

Now when Oracle tries to merge these data into t1, the condition

ON (tt1.a = tt2.a)

returns two record to be returned for t1.a = 1.

So when it tries to update these records Oracle get confused and raises error “ORA-30926: unable to get a stable set of rows in the source tables”.

Filed under: Oracle Concept by admin

2 Comments »