Tables and Constraints - Pro Oracle Database 12c Administration - page 201

and a.constraint_type = 'R'
and b.owner = upper('&table_owner');
spo off;
This script generates a file, named dis_dyn.sql, that contains the SQL statements to disable all the foreign key
constraints for a user.
Enabling Constraints
This section contains a few scripts to help you enable constraints that you've disabled. Listed next is a script
that creates a file with the SQL statements required to reenable any foreign key constraints for tables owned by a
specified user:
set lines 132 trimsp on head off feed off verify off echo off pagesize 0
spo enable_dyn.sql
select 'alter table ' || a.table_name
|| ' enable constraint ' || a.constraint_name || ';'
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner');
spo off;
When enabling constraints, by default, Oracle checks to ensure that the data don't violate the constraint
definition. If you're fairly certain that the data integrity is fine and that you don't need to incur the performance hit by
revalidating the constraint, you can use the NOVALIDATE clause when reenabling the constraints. Here is an example:
select 'alter table ' || a.table_name
|| ' modify constraint ' || a.constraint_name || ' enable novalidate;'
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner');
The NOVALIDATE clause instructs Oracle not to validate the constraints being enabled, but it does enforce that any
new DML activities adhere to the constraint definition.
In multiuser systems the possibility exists that another session has inserted data into the child table while the
foreign key constraint was disabled. If that happens, you see the following error when you attempt to reenable the
foreign key:
ORA-02298: cannot validate (<owner>.<constraint>) - parent keys not found
In this scenario, you can use the ENABLE NOVALIDATE clause:
SQL> alter table emp enable novalidate constraint emp_dept_fk;