Real Scenario 6: ORA-00001: unique constraint (XYZ.EXAMPLE) violated

Real Scenario 6: ORA-00001: unique constraint (XYZ.EXAMPLE) violated

Solutions:

We can solve this issue in the below ways:

****Temporary fix***************

we can allow the number of rejects by using STOP ON ERRORS Property in Session properties to recject the records.

so that all the records whcih are rejecting will be skipped and job will be completed succesfully.

******Permenant fix*************

1->we have to identify the Duplicate record.

   How to find the Duplicate Records in a table:

       ** we need to check the Primary key Constarint which is created on this table.

Example:

lets Say EXAMPLE table is having a Primary key Constraint EXAMPLE_PK which is defined on two columns USER_NAME and USER_ID.

so if my job is failed due to unique constraint violated issue then i need to do the below to check the duplicate records in the table.

   Query to find Duplicates:
      
       select count(*),A.USER_NAME,A.USER_ID from XYZ.EXAMPLE A GROUP BY A.USER_NAME,A.USER_ID having count(*)>1;

this query will fetch the duplicate record in the table EXAMPLE.

2-> if we found any Duplicate in EXAMPLE Table then please keep the valid records and delete the Duplicate Data in the table.

3-> if the indexes are Disabled on the Table, please recreate the indexes.

4-> Please restart the job, it should be completed.

5-> for a permenant fix, please Check the Source side why we got Duplicate records.

if we did not get Any Duplicate Records in Step1(permenant fix), still our job failed due to Unique Constraint error, then we need to check the below:

6-> if there are no Duplicate records in table EXAMPLE, then we need to check the Source of EXAMPLE Table.

Example:

lets say if EXAMPLE table is Sourcoing from SOURCE_EXAMPLE table then do the below.

     to find the Duplicates in the Source table:

select count(*),B.USER_NAME,B.USER_ID from XYZ.SOURCE_EXAMPLE B GROUP BY B.USER_NAME,B.USER_ID having count(*)>1;

*******
Note: if the Primary key is defined on Different Columns, please arrange the Query According to the same.

7->in Source if the records are not valid,then please delete the invalid records. if they are valid then please Sync the Source and tager table Constraint keys.( define Primary keys on Proper fields in match with Source and target tables).

8-> after u done with Deletion of Duplicates, please restrart the job.(make Sure Indexes are Enabled).

Thanks,
Anilkumar.