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

advertisements

_____________________________________________________________________________________________________________________

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>

_____________________________________________________________________________________________________________________

Website Stats