Introduction To Oracle9i: Advanced SQL Page 205 Managing Constraint Exceptions Whenever a constraint is created or enabled, if any rows already exist in the table which violate the constraint then the constraint is not enabled. The specific rows that violate the constraint are exceptions and may be logged in an exception table. A list of exceptions may be created whenever the constraint is enabled, either implicitly (CREATE TABLE, ALTER TABLE ... ADD) or explicitly (ALTER TABLE ... ENABLE). About Exception Tables Exception tables must be managed entirely by the user: · The exception table must be manually created. The database administrator can provide a script file named UTLEXCPT.SQL to create the exception table. (The exact path of this file may differ depending upon the operating system and the database version in use.) SQL> @ d:/oracle/rdbms/admin/UtlExcpt.SQL · · The exception table may be queried to identify the specific rows which violate the constraints. When exceptions are corrected (and the constraint possibly enabled) then the exception row must be manually removed from the exception table. Otherwise, the row will remain in the exception table and incorrectly imply that an exception still exists. Exception Table Description ROW_ID ROWID OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) CONSTRAINT VARCHAR2(30) Example (Storing Exceptions) SQL> @ d:\oracle\rdbms\admin\UtlExcpt.SQL SQL> ALTER TABLE employee ENABLE CONSTRAINT employee_department_fk EXCEPTIONS INTO exceptions; Table altered. Copyright © 2004 Sideris Courseware Corporation. Reproduction strictly prohibited.