I am a simple Senior Oracle DBA Consultant....

Oracle Certified (Database 9i/10g/11g
& Application 11i), dynamic, self-motivated and results-oriented
professional with over 16 years of broad- based experience in Database
Management, Administration, Backup & Recovery, Tuning and Oracle
Application Concepts and Architecture using Oracle Technologies (Database,
Oracle Application and Middleware). Expertise in high-technology time and cost
based solutions, quality customer service and commitment to excellence.

Thursday, July 11, 2013

ORA-00001: unique constraint(schema.table_name) violated

I faced an error from a production ERP system, which the trace file shows "Unique constraint violated".  Actually this process inserts rows from temporary table to target table.  The temporary table has got 1000 of rows.  Easy way to check the error,

1) check which columns are part of the unique constraints

    select constraint_name,constraint_type,search_condition from dba_constraints where table_name=<TABLE_NAME>;

In my case the target table has got check constraints, so can't find it in dba_constraints.

2) check unique index for the target table

    select index_name,uniqueness from dba_indexes where table_name=<TABLE_NAME>;
    select column_name,column_position from dba_ind_columns where index_name=<PREVIOUS STEP VALUE>;

3) check target table rows compare with temporary table rows for the specific unique index columns.

4) Correct the data.