Saturday, September 20, 2008

How to Disable and Enable all constraints using SQL

I have written two scripts. These scripts will disable and enable of entire constraints of ARJU schema. However if you want to enable of disable another schema then change the AND t.owner='ARJU' line inside script and change text ARJU.

Disable constraints of entire ARJU schema


set feedback off
set verify off
set echo off
prompt This script is going to disable constraints ....
set termout off
set pages 500
set heading off
set linesize 150
spool cons_disable.sql
select 'spool constraint_disable.log;' from dual;
select 'ALTER TABLE ARJU.'||c.table_name||' DISABLE CONSTRAINT '||constraint_name||' CASCADE;'
from dba_constraints c, dba_tables t
where c.table_name = t.table_name
AND t.owner='ARJU';
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@cons_disable.sql;
exit
/


Enable Constraints of Entire ARJU Schema

set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool cons_enable.sql
select 'spool cons_enable.log;' from dual;
select 'ALTER TABLE ARJU.'||c.table_name||
' ENABLE CONSTRAINT '||constraint_name||' ;'
from dba_constraints c, dba_tables u
where c.table_name = u.table_name AND u.owner='ARJU';
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@cons_enable;
exit
/

Related Documents
ORA-02297: cannot disable constraint -dependencies exist
Type of constraint in oracle
How to know list of constraints and reference constraints in a table

No comments: