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';