Oracle Disable/Enable constraints for schema

Oracle Disable/Enable constraints for schema
Oracle constraint Disable
Disable all table constraints in Oracle




spool disable_cons.sql
select 'alter table '||owner||'.'||table_name||' disable constraint '
||constraint_name||'CASCADE ;'
from all_constraints
where status= 'ENABLED'
and owner =upper('&&Owner')
and constraint_type ='P'
union
select 'alter table '||owner||'.'||table_name||' disable constraint '
||constraint_name||' CASCADE ;'
from all_constraints
where status = 'ENABLED'
and CONSTRAINT_TYPE ='R'
and r_constraint_name in
(select constraint_name from all_constraints
where owner =upper('&&Owner')
and CONSTRAINT_TYPE ='P'
)

/
spool off
spool disable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||' disable;'
from all_triggers
where owner =upper('&&Owner')
and status ='ENABLED'
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80


@disable_cons.sql ---- run two time
@disable_triggers.sql

check

select OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
STATUS
from dba_constraints
where STATUS = 'ENABLED'
and owner =upper('&&Owner')
order by OWNER, TABLE_NAME, CONSTRAINT_NAME







after diable the constraints then enable them via this script




spool enable_cons.sql
select 'alter table '||owner||'.'||table_name||' ENABLE constraint '
||constraint_name||';'
from all_constraints
where status= 'DISABLED'
and owner =upper('&&Owner')
and constraint_type ='P'
union
select 'alter table '||owner||'.'||table_name||' ENABLE constraint '
||constraint_name||';'
from all_constraints
where status = 'DISABLED'
and CONSTRAINT_TYPE ='R'
and r_constraint_name in
(select constraint_name from all_constraints
where owner =upper('&&Owner')
and CONSTRAINT_TYPE ='P'
)
/
spool off
spool enable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||' enable;'
from all_triggers
where owner =upper('&&Owner')
and status ='DISABLED'
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80

@enable_cons.sql
@enable_triggers.sql



Then check

select OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
STATUS
from dba_constraints
where STATUS = 'DISABLED'
and owner =upper('&&Owner')
order by OWNER, TABLE_NAME, CONSTRAINT_NAME


or






BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'ENABLED'
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name||' CASCADE ;' );
END LOOP;
END;
/




BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'DISABLED'
ORDER BY c.constraint_type)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/