0

In Oracle I have a lot of tables in a given schema. Some of the table constraints in the schema have been disabled.

I have to know for all tables in a given schema with any of the constraints deactivated, what are the (data) rows that would not fulfil the deactivated constraints.

Question

Is there a way to easily collect all the rows that would produce an error if I were to decide to enable the constraints again?

So far, I would try to go constraint by constraint, create a query for each table and run the query. But I wonder whether there is anything that may do the work quickly and less error prone. If possible, the solution should use the information available in view USER_CONSTRAINTS or any similar one.

I have checked the constraint documentation in Oracle, in which the constraint clause may use ENABLE NOVALIDATE, which allows for having non-compliant rows of data for the existing values until they are modified.

However, I am looking for a solution that would allow me to fix the data before I re-enable the constraints.

1

Reporting Constraint Exceptions

You must create an appropriate exceptions report table to accept information from the EXCEPTIONS option of the ENABLE clause before enabling the constraint. You can create an exception table by executing the UTLEXCPT.SQL script or the UTLEXPT1.SQL script.

The following statement attempts to validate the PRIMARY KEY of the dept table, and if exceptions exist, information is inserted into a table named EXCEPTIONS:

ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;

If duplicate primary key values exist in the dept table and the name of the PRIMARY KEY constraint on dept is sys_c00610, then the following query will display those exceptions:

SELECT * FROM EXCEPTIONS;

The following exceptions are shown:

fROWID               OWNER      TABLE_NAME      CONSTRAINT
------------------  ---------  --------------  -----------
AAAAZ9AABAAABvqAAB  SCOTT      DEPT            SYS_C00610 
AAAAZ9AABAAABvqAAG  SCOTT      DEPT            SYS_C00610
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.