Check for violate rows in the foreign key Constraint
Clean the rows that violate the constraint, For this you need to create an EXCEPTIONS table created in your currently connected schema.
Note: If you don’t have an EXCEPTIONS table, use this script present in RDBMS folder of Oracle Home : rdbms/admin/utlexcpt.sql
Follow the following steps for find the row that violate the foreign key constraint:
1. Create an exceptions table.
SQL> @?/rdbms/admin/utlexcpt.sql;
2. Populate the EXCEPTIONS table with the rows that violate the constraint, using the EXCEPTIONS INTO clause.
SQL> alter table emp modify constraint emp_dept_fk validate exceptions into exceptions;
Note: This statement while executing still throws the ORA-02298 error as long as there are rows that violate the constraint. The statement also inserts records into the EXCEPTIONS table for any bad rows. You use the ROW_ID column of the EXCEPTIONS table to remove any records that violate the constraint. In Example you find that only one row needs to be removed from the EMP table:
3. Check the Exception table for rows.
SQL> select * from exceptions;
Output:
ROW_ID OWNER TABLE_NAME CONSTRAINT ------------------ ----- ---------- -------------------- AAAFKQAABAAAK8JAAB SCOTT EMP EMP_DEPT_FK
