Disables/Enables all the user constraints


/* Script that Disables all the user constraints */

set heading off 
set feedback off
set verify off
set linesize 1024
spool disable.sql
SELECT 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME
||';' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R';
SELECT 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME
||';' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='C';
SELECT 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME
||';' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE NOT IN ('R','C') ;
spool off
CL SCR
prompt 'Disabling Constraints.......'
@disable.SQL
prompt 'Disabled Constraints'
set heading on
set feedback on
set verify on

/*Script that enables all the user constraints. */

set heading off 
set feedback off
set verify off
set linesize 1024
spool enable.sql
SELECT 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||CONSTRAINT_NAME
||';' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE NOT IN ('R','C');
SELECT 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||CONSTRAINT_NAME
||';' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R';
SELECT 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||CONSTRAINT_NAME
||';' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='C';
spool off
CL SCR
prompt 'Enabling Constraints.......'
@enable.SQL
prompt 'Constraints Enabled'
set heading on
set feedback on
set verify on

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: