Identifying rows that cause ORA-02298 when enabling constraints

When doing bulk loads of data into tables it is quicker to disable the contraints on the tables and then enable them at the end.

One of the problems with this approach is that the constraint may not validate because of rogue data. One of the great features is being able to log the offending rows an exceptions table.

Lets take a look at an example

SQL> ALTER TABLE EMP ENABLE CONSTRAINT EMP_FK2;

ALTER TABLE EMP ENABLE CONSTRAINT EMP_FK2 

                                                                                  *

ERROR at line 1:

ORA-02298: cannot validate (EMP_FK2) - parent keys not found

Lets identify the offending rows:

1. Create the exceptions table using utlexcpt.sql (utlexcpt.sql uses rowid, utlexpt1.sql uses urowuid)

SQL>  @?/rdbms/admin/utlexcpt.sql

2. Try and enable the constraint again

SQL> ALTER TABLE EMP ENABLE CONSTRAINT EMP_FK2 EXCEPTIONS INTO EXCEPTIONS;

ALTER TABLE EMP ENABLE CONSTRAINT EMP_FK2 EXCEPTIONS INTO EXCEPTIONS

                                  *

ERROR at line 1:

ORA-02298: cannot validate (EMP_FK2) - parent keys not found

3. Have a look at the exceptions table

SQL> select * from exceptions;

ROW_ID             OWNER                          TABLE_NAME            CONSTRAINT

------------------ ------------------------------ --------------------- ---------------------

AAANd/AARAAABAmAAs SCOTT                          EMP                   EMP_FK2

4. We can now identify the row as follows:

SQL> select a.* from EMP a, EXCEPTIONS e

     where a.rowid = e.row_id;

Delete offending rows or add parent rows as necessary