ORA-30926 unable to get a stable set of rows in the source tables

Question

I am getting      ORA-30926  unable to get a stable set of rows in the source tables   in the following query     MERGE INTO table 1 a       USING         SELECT a ROWID row id   Y                FROM table 1 a  table 2 b  table 3 c               WHERE a mbr   c mbr               AND b head   c head               AND b type of action  lt  gt   6   src               ON   a ROWID   src row id     WHEN MATCHED THEN UPDATE SET in correct    Y     I ve ran table 1 it has data and also I ve ran the inside query  src  which also has data    Why would this error come and how can it be resolved

User · Answer

Had the error today on a 12c and none of the existing answers fit (no duplicates, no non-deterministic expressions in the WHERE clause). My case was related to that other possible cause of the error, according to Oracle's message text (emphasis below):

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.

The merge was part of a larger batch, and was executed on a live database with many concurrent users. There was no need to change the statement. I just committed the transaction before the merge, then ran the merge separately, and committed again. So the solution was found in the suggested action of the message:

Action: Remove any non-deterministic where clauses and reissue the dml.

User · Answer

You re probably trying to to update the same row of the target table multiple times   I just encountered the very same problem in a merge statement I developed   Make sure your update does not touch the same record more than once in the execution of the merge

User · Answer

How to Troubleshoot ORA-30926 Errors   Doc ID 471956 1    1  Identify the failing statement     alter session set events    30926 trace name errorstack level 3        or     alter system set events    30926 trace name errorstack off        and watch for  trc files in UDUMP when it occurs   2  Having found the SQL statement  check if it is correct  perhaps using explain plan or tkprof to check the query execution plan  and analyze or compute statistics on the tables concerned if this has not recently been done  Rebuilding  or dropping recreating  indexes may help too    3 1  Is the SQL statement a MERGE   evaluate the data returned by the USING clause to ensure that there are no duplicate values in the join  Modify the merge statement to include a deterministic where clause  3 2  Is this an UPDATE statement via a view   If so  try populating the view result into a table and try updating the table directly    3 3   Is there a trigger on the table  Try disabling it to see if it still fails    3 4   Does the statement contain a non-mergeable view in an  IN-Subquery   This can result in duplicate rows being returned if the query has a  FOR UPDATE  clause  See Bug 2681037   3 5  Does the table have unused columns  Dropping these may prevent the error    4   If modifying the SQL does not cure the error  the issue may be with the table  especially if there are chained rows   4 1  Run the    ANALYZE TABLE  VALIDATE STRUCTURE CASCADE    statement on all tables used in the SQL to see if there are any corruptions in the table or its indexes   4 2  Check for  and eliminate  any CHAINED or migrated ROWS on the table  There are ways to minimize this  such as the correct setting of PCTFREE  Use Note 122020 1 - Row Chaining and Migration  4 3  If the table is additionally Index Organized  see   Note 102932 1 - Monitoring Chained Rows on IOTs

User · Answer

SQL Error  ORA-30926  unable to get a stable set of rows in the source tables 30926  00000 -   quot unable to get a stable set of rows in the source tables quot   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   This Error occurred for me because of duplicate records 16K  I tried with unique it worked   but again when I tried merge without unique same proble occurred Second time it was due to commit after merge if commit is not done same Error will be shown  Without unique  Query will work if commit is given after each merge operation

User · Answer

This is usually caused by duplicates in the query specified in USING clause  This probably means that TABLE A is a parent table and the same ROWID is returned several times   You could quickly solve the problem by using a DISTINCT in your query  in fact  if  Y  is a constant value you don t even need to put it in the query    Assuming your query is correct  don t know your tables  you could do something like this     MERGE INTO table 1 a       USING         SELECT distinct ta ROWID row id               FROM table 1 a  table 2 b  table 3 c               WHERE a mbr   c mbr               AND b head   c head               AND b type of action  lt  gt   6   src               ON   a ROWID   src row id     WHEN MATCHED THEN UPDATE SET in correct    Y

User · Answer

I was not able to resolve this after several hours   Eventually I just did a select with the two tables joined  created an extract and created individual SQL update statements for the 500 rows in the table   Ugly but beats spending hours trying to get a query to work

User · Answer

A further clarification to the use of DISTINCT to resolve error ORA-30926 in the general case  You need to ensure that the set of data specified by the USING   clause has no duplicate values of the join columns  i e  the columns in the ON   clause  In OP s example where the USING clause only selects a key  it was sufficient to add DISTINCT to the USING clause   However  in the general case the USING clause may select a combination of key columns to match on and attribute columns to be used in the UPDATE     SET clause  Therefore in the general case  adding DISTINCT to the USING clause will still allow different update rows for the same keys  in which case you will still get the ORA-30926 error  This is an elaboration of DCookie s answer and point 3 1 in Tagar s answer  which from my experience may not be immediately obvious

[oracle] ORA-30926: unable to get a stable set of rows in the source tables

Examples related to oracle

Examples related to sql-merge