ORA 30926 - Unable to get stable set of rows in source tables

Question

I recently encountered a Strange issue on Merge statement. It failed with ORA 30926 error.

I have already checked for the below pitfalls,

1. Duplicate records check in Source and Target table – Came out clean
2. Source and Target tables Analyze structure – Came out clean
3. Source and Target tables Indexes Analyze structure – Came out clean
4. Same Merge SQL when DBA tried in SYS user – Worked. Still Puzzling
5. Same Merge SQL runs successfully in Copy table of Target – Worked. Still Puzzling
6. DBA Bounced the TEST server. Though not convincing wanted to give a try as the issue appears to be strange – Didn’t Workout
7. Gathered the statistics
8. Truncate the Original target table and reload from Copy table and try the Merge again - Didn't Workout. Failed with same error

Nutshell Script:

MERGE INTO TGT_SCHEMA.EMP T 
USING SRC_SCHEMA.S_EMP S 
ON 
(
T.EMPLOYEE_NO = S.EMPLOYEE_NO AND
T.START_DATE = S.START_DATE
)

Unique Index (EMPLOYEE_NO, START_DATE) exists on Target table and a normal Index of same combination exists on Source table. Target table is a partitioned table and there are some VPD policies applied to other columns.

My Database version : Oracle 11.2.0.3.0

Answer 1

If you really checked everything you said correctly, then this is a bit of a puzzler. I think #4 in your diagnostic checklist may be telling: that the same statement worked when executed as SYS.

For fun, check to see where there are any VPD policies on either table (DBMS_RLS package). If there are, try to disable them and retry the merge.

Answer 2

This error happens on MERGE when the USING clause returns more than one row for one or more target rows according to your matching criteria. Since it can't know which of two updates to do first, it gives up.

Run:

SELECT matching_column1, ..matching_ColumnN, count(*)
FROM (
     <your USING query>
    )
group by matching_column1, ..matching_ColumnN
having count(*) > 1

To find the offending source data. At that point either modify your USING query to resolve it, change your matching criteria, or clean up bad data - whichever is appropriate.

EDIT - Add another item:

One other possibility - you will get this error if you try to update a column in your target that is referenced in your ON clause.

So make sure that you are not trying to UPDATE the EMPLOYEE_NO or START_DATE fields.