Enable and Disable all constraints in Oracle

Check the constraint present on table and owner

col type format a10
col cons_name format a24
COL TABLE_NAME FOR A25
select decode(constraint_type,
'C', 'Check',
'O', 'R/O View',
'P', 'Primary',
'R', 'Foreign',
'U', 'Unique',
'V', 'Check view') type
, constraint_name cons_name
, TABLE_NAME
from dba_constraints
where table_name='EMP'
order by 1;


Enable and Disable the Constraint

--Enable the Constraint
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

--Disable the constraint
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;


Disable all the constraints with CASCASE in Schema

select 'alter table '||owner||'.'||table_name|| ' disable constraint '||constraint_name||';' from all_constraints where delete_rule = 'CASCADE' and owner = 'HR';

 
Enable all the constraints in Schema

select 'alter table '||owner||'.'||table_name|| ' enable constraint '||constraint_name||';' from all_constraints where owner = 'HR';


Disable all the constraints

select 'alter table '||owner||'.'||table_name|| ' disable constraint '||constraint_name||';' from all_constraints where owner = 'HR';