How to disable all constraints on a table or schema - Oracle sql Script

Overview: These are the sql commands to enable/disable oracle constraints.
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
Suppose if we have multiple constraints to disable or enable, in this situation executing these commands for each and every constraints is difficult.
Sqlplus provides spooling feature to generate scripts. Using these scripts you can perform the constraint disable/enable in bulk (schema,table,etc).


Generate disable script
set pages 1000
set lines 120
set echo off feedback off verify off heading off
set termout off
spool disble_consts.sql
select 'spool disbable_consts.log;' from dual;
select 'ALTER TABLE '||tabs.owner||'.'||cons.table_name||' DISABLE CONSTRAINT '||constraint_name||' CASCADE;'
from dba_tables tabs, dba_constraints cons
where tabs.owner='SCOTT'
AND cons.table_name = tabs.table_name;
select 'exit;' from dual;
spool off
@disable_consts.sql;
exit
/


Generate enable script
set pages 1000
set lines 120
set echo off feedback off verify off heading off
set termout off
spool enable_consts.sql
select 'spool enable_consts.log;' from dual;
select 'ALTER TABLE '||tabs.owner||'.'||cons.table_name||' ENABLE CONSTRAINT '||constraint_name’;'
from dba_tables tabs, dba_constraints cons
where tabs.owner='SCOTT'
AND cons.table_name = tabs.table_name;
select 'exit;' from dual;
spool off
@enable_consts.sql;
exit
/


For table you have to add one more condition on script and the condition is
and cons.table_name=<table_name>
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google