April 28, 2021
I got ” ORA-30926: unable to get a stable set of rows in the source tables ” error in Oracle.
ORA-30926: unable to get a stable set of rows in the source tables
Details of error are as follows.
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: Remove any non-deterministic where clauses and reissue the dml.
unable to get a stable set of rows in the source tables
This ORA-30926 errors are related with the stable set of rows could not be got because of large dml activity or a non-deterministic where clause.
To solve this error, you should remove any non-deterministic where clauses and reissue the dml.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
1,361 views last month, 1 views today
About Mehmet Salih Deveci
I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].- -Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için [email protected] a mail atabilirsiniz.
In my recent project I get this error while I’m using Merge in Oracle. After some searching I found the problem and decided to write a post about it:)
The reason of this error is source table. In merge queries we have source table and target table. We make some operations (Update, Insert, Delete) according to source table. We connect target table and source table in ON clause of Merge. And merge expects that source table returns unique values according to ON clause. If your columns, that you use in the ON clause, don’t provide unique key feature, you will get this error too.. Don’t forget primary key is a unique key;)
I think this is reasonable expectation. Think about it: what happens if source table returns non-unique values? Which data should be used to update rows? How can Oracle select this? So this is why Oracle expect uniqueness in On clause.
Let me make you see this error with example:
CREATE TABLE source_table (
col1 NUMBER,
col2 VARCHAR2(10),
col3 VARCHAR2(10)
);
INSERT INTO source_table (col1, col2, col3) VALUES (1, 'a', 'w');
INSERT INTO source_table (col1, col2, col3) VALUES (1, 'b', 'x');
INSERT INTO source_table (col1, col2, col3) VALUES (2, 'c', 'y');
INSERT INTO source_table (col1, col2, col3) VALUES (3, 'c', 'z');
COMMIT;
CREATE TABLE target_table (
col1 NUMBER,
col2 VARCHAR2(10),
col3 VARCHAR2(10)
);
INSERT INTO target_table (col1, col2, col3) VALUES (1, 'b', 'z');
INSERT INTO target_table (col1, col2, col3) VALUES (3, 'd', 'w');
COMMIT;
In above queries we just simply create sample data. Now we are going to merge two table.
MERGE INTO target_table trg
USING (--Actually we can simply write source_table for this example but I want to write Select:)
SELECT col1, col2, col3
FROM source_table
) src
ON (trg.col1 = src.col1)
WHEN MATCHED THEN UPDATE SET --Don't forget you cannot update columns that included in ON clause
trg.col2 = src.col2,
trg.col3 = src.col3
WHEN NOT MATCHED THEN INSERT
(
col1,
col2,
col3
)
VALUES
(
src.col1,
src.col2,
src.col3
);
COMMIT;
Aaand boom! We get the error! We used col1 column for merge operation. And in our source table col1 is not unique. We have two different rows in source_table that col1 column is '1'. So our merge query couldn’t decide the which one has desirable value. (1,'a','w') OR (1,'b',x')? You couldn’t decide it too, didn’t you:)
Now let’s make correct this merge query. I’m looking rows and as I see col1 AND col2 provide unique values together. If we redesign our query and make the join on these values we will successfully make the operations.
MERGE INTO target_table trg
USING source_table src --Now I simply write the table name:)
ON (
trg.col1 = src.col1 AND
trg.col2 = src.col2
)
WHEN MATCHED THEN UPDATE SET --Don't forget you cannot update columns that included in ON clause
trg.col3 = src.col3
WHEN NOT MATCHED THEN INSERT
(
col1,
col2,
col3
)
VALUES
(
src.col1,
src.col2,
src.col3
);
COMMIT;
In the final target table our values will be like this:
I hope my explanations are clear:)
Developers Rock!!!
Just a quick blog post on MERGE and the “unable to get a stable set of rows” error that often bamboozles people. This is actually just the script output from a pre-existing YouTube video (see below) that I’ve already done on this topic, but I had a few requests for the SQL example end-to-end, so here it is.
Imagine the AskTOM team had a simple table defining the two core members, Chris Saxon and myself. But in the style of my true Aussie laziness, I was very slack about checking the quality of the data I inserted.
SQL> create table oracle_team (
2 pk number primary key,
3 first_name varchar2(10),
4 last_name varchar2(10)
5 );
Table created.
SQL>
SQL> insert into oracle_team (pk, first_name, last_name) values (1, 'Connor', 'Macdonald');
1 row created.
SQL> insert into oracle_team (pk, first_name, last_name) values (3, 'kris', 'saxon');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from oracle_team;
PK FIRST_NAME LAST_NAME
---------- ---------- ----------
1 Connor Macdonald
3 kris saxon
2 rows selected.
You can see that the data is garbage. Both of our names are wrong so they need fixing. So I build a table called FIXES which lets people “queue up” fix requests to the table. I’ll add the 2 obvious fixes to that table.
SQL> create table fixes (
2 team_pk number,
3 first_name varchar2(10),
4 last_name varchar2(10),
5 requested date
6 );
Table created.
SQL> insert into fixes values (1, 'Connor', 'McDonald',sysdate);
1 row created.
SQL> insert into fixes values (3, 'Chris', 'Saxon',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from fixes;
TEAM_PK FIRST_NAME LAST_NAME APPLIED
---------- ---------- ---------- ---------
1 Connor McDonald 18-FEB-19
3 Chris Saxon 18-FEB-19
2 rows selected.
To apply those fixes to the table, a simple MERGE command is all I need. Notice that MERGE does not have to be a “full” merge (ie, update AND insert), you can pick and choose what elements you want. MERGE is very powerful and flexible in that regard.
SQL>
SQL>
SQL> merge into oracle_team target
2 using (select team_pk, first_name, last_name
3 from fixes
4 ) source
5 on (target.pk = source.team_pk)
6 when matched then
7 update set
8 target.first_name = source.first_name,
9 target.last_name = source.last_name
10
SQL> pause
SQL> /
2 rows merged.
SQL>
SQL> select * from oracle_team;
PK FIRST_NAME LAST_NAME
---------- ---------- ----------
1 Connor McDonald
3 Chris Saxon
2 rows selected.
So all looks well. Let me now show how what seems like a simple repeat of that operation can get us into trouble. I’ve dropped all the tables, so that I can recreate the demo from scratch.
SQL>
SQL> create table oracle_team (
2 pk number primary key,
3 first_name varchar2(10),
4 last_name varchar2(10)
5 );
Table created.
SQL>
SQL> insert into oracle_team (pk, first_name, last_name) values (1, 'Connor', 'Macdonald');
1 row created.
SQL> insert into oracle_team (pk, first_name, last_name) values (3, 'kris', 'saxon');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from oracle_team;
PK FIRST_NAME LAST_NAME
---------- ---------- ----------
1 Connor Macdonald
3 kris saxon
2 rows selected.
SQL> create table fixes (
2 team_pk number,
3 first_name varchar2(10),
4 last_name varchar2(10),
5 applied date
6 );
Table created.
This time we’ll assume that repeated fix requests have come in for a single AskTOM team member (PK=1). My first fix request was to the change the “Mac” to “Mc” in McDonald, but then I got all picky and realised that I’d like to have a capital “D” in McDonald. Fussy fussy fussy 
SQL> insert into fixes values (1, 'Connor', 'Mcdonald',sysdate-1);
1 row created.
SQL> insert into fixes values (1, 'Connor', 'McDonald',sysdate);
1 row created.
SQL> insert into fixes values (3, 'Chris', 'Saxon',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from fixes;
TEAM_PK FIRST_NAME LAST_NAME APPLIED
---------- ---------- ---------- ---------
1 Connor Mcdonald 17-FEB-19
1 Connor McDonald 18-FEB-19
3 Chris Saxon 18-FEB-19
3 rows selected.
SQL>
Look what happens now when I re-attempt the MERGE.
SQL>
SQL>
SQL> merge into oracle_team target
2 using (select team_pk, first_name, last_name
3 from fixes
4 ) source
5 on (target.pk = source.team_pk)
6 when matched then
7 update set
8 target.first_name = source.first_name,
9 target.last_name = source.last_name
10
SQL> pause
SQL> /
merge into oracle_team target
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
Conceptually this example (hopefully) makes it clear why the error occurred. Depending on which FIXES row for my row in the target table (PK=1) the database sees first, the end result of the MERGE could be different. And we really can’t allow that because it means the results are pseudo-random.
So how can we fix these things? We need a stable set of rows in the sense that the MERGE results should never be questionable based on order in which we process the input set of rows. In the example above, we could probably make an assumption that the last fix request for a given primary key value is the one that should always take precedence. Hence the source set of fixes can be altered as below.
SQL>
SQL>
SQL> select *
2 from
3 (select team_pk,
4 first_name,
5 last_name,
6 row_number() over ( partition by team_pk order by applied desc ) as r
7 from fixes
8 ) where r = 1;
TEAM_PK FIRST_NAME LAST_NAME R
---------- ---------- ---------- ----------
1 Connor McDonald 1
3 Chris Saxon 1
2 rows selected.
which then becomes an input into the MERGE command and the error is avoided.
SQL>
SQL> merge into oracle_team target
2 using
3 ( select *
4 from
5 (select team_pk,
6 first_name,
7 last_name,
8 row_number() over ( partition by team_pk order by applied desc ) as r
9 from fixes
10 ) where r = 1
11 ) source
12 on (target.pk = source.team_pk)
13 when matched then
14 update set
15 target.first_name = source.first_name,
16 target.last_name = source.last_name
17
SQL> /
2 rows merged.
SQL>
SQL> select * from oracle_team;
PK FIRST_NAME LAST_NAME
---------- ---------- ----------
1 Connor McDonald
3 Chris Saxon
2 rows selected.
The video walking through this demo came from an Office Hours session, and you can watch it below
The error ORA-30926 associated with Merge statement should be one of the easily encountered error while working with Merge. The Update part of the statement is responsible to cause the error.
The reason to hit this error is: assume that there is a table by the name target_t whose column “Description” needs to be updated with value of “Description” column of table source_t. When there is more than 1 value of source_t.Description (table.column_name) to be updated in target_t.Description column – for a particular join condition – the database is not sure to use which source_t.Description’s vlaue to update the target_t table with. Such situation causes the database to throw error “unable to get a stable set of rows in the source tables” – note the “source tables” mentioned in the error message, this gives us hint that there is something wrong in the source table, here source_t.
A simple demonstration to reproduce the error
Create 2 tables source_t and target_t
SQL> CREATE TABLE source_t AS SELECT CASE WHEN MOD(level, 2) = 0 THEN 10 ELSE 20 END AS status, 'Description of status ' || level AS description FROM dual CONNECT BY level <= 3; Table created. SQL> select * from source_t order by status; STATUS DESCRIPTION ---------- --------------------------------- 10 Description of status 2 20 Description of status 3 <-- See here, for same status 20 there are 2 different Description values 20 Description of status 1 <-- SQL> CREATE TABLE target_t AS SELECT CASE WHEN MOD(level, 2) = 0 THEN 10 ELSE 20 END AS status, 'To be updated ' || level AS description FROM dual CONNECT BY level <= 6; Table created. SQL> select * from target_t; STATUS DESCRIPTION ---------- ----------------------------------------- 20 To be updated 1 10 To be updated 2 20 To be updated 3 10 To be updated 4 20 To be updated 5 10 To be updated 6 6 rows selected.
Let’s update the Description column of target_t table having status 20 with the Description column of source_t table using Merge.
SQL> MERGE INTO target_t tgt USING source_t src ON (tgt.status=20 and tgt.status = src.status) WHEN MATCHED THEN UPDATE SET tgt.description = src.description ; USING source_t src * ERROR at line 3: ORA-30926: unable to get a stable set of rows in the source tables
Since source_t.description column has 2 different values: Description of status 3 and Description of status 1 for the same status 20 – database is unclear as to which value it should use to update the target_t table and hence error ORA-30926 is raised.
To avoid such error we should know our data – understand data in the tables are not redundant/duplicate with table constraints in place, ensure that the join condition is appropriate,.
And equivalent errors using just UPDATE statement are:
SQL> update (select src.description src_desc,tgt.description tgt_desc,tgt.status from source_t src ,target_t tgt where src.status=tgt.status and tgt.status=20 ) set tgt_desc = src_desc; set tgt_desc = src_desc * ERROR at line 7: ORA-01779: cannot modify a column which maps to a non key-preserved table SQL> update target_t tgt SET tgt.description = (select src.description from source_t src where src.status=tgt.status ) where exists (select 1 from source_t src where src.status=tgt.status ) and tgt.status=20; SET tgt.description = (select src.description * ERROR at line 2: ORA-01427: single-row subquery returns more than one row
Now you might think that if I had come across the error “single-row subquery returns more than one row” instead of Merge’s ORA-30926 I would have quite easily nailed the reason for the error.
One of the reason for this post is that I recently encountered this error and spent an hour or so before finding the cause of the error – had I known that the issue is with the source table, I could have resolved without much spending time on analyzing.
For more understanding you gotta read Tom’s explanation on the same topic.