ADD/ENABLE constraint exception like ORA-02437, ORA-02299 etc
Today while working on one assignment I came across situation where I have to enable a unique key constraint on the table which some one disabled and forget due to which many duplicate records get entered and started creating problem for the application.
Now challenge is to find out all those records there are ways to find duplicates by writing some queries but this is easiest and faster way by using Oracle Exceptions table.
I tried Exceptions table which gives you exact rows ids which violating the constraint.
Create a exception table as per below definition in database:
CREATE TABLE EXCEPTIONS (ROW_ID UROWID,
OWNER VARCHAR2(30),
TABLE_NAME VARCHAR2(30),
CONSTRAINT_NAME VARCHAR2(30));
When you execute a constraint command (like alter table … to add constraint or alter table …to enable constraint) you can mention the following clause to use exception table:
alter table nits.test add constraint uk1 unique(fname,lname) exceptions into exceptions;
Now if for some reason the constraint command fails, the row-id for each row causing the error will be stored in this exception table.
The exception table only contains the row-id of row which creating trouble, so you have to join the exceptions table with the the table you are adding or enabling the constraint for to get the troubling rows.
select * from nits.test where rowid in (select row_id from exceptions);
This was really helpful for me so like to share with you all, hope will find it helpful 🙂