Re: Wanted:PL/SQL to Disable & Enable Constraints

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Tue, 22 Jul 2003 16:24:06 GMT
Message-ID: <MPG.198704fe8a37e44c9897f5@news.la.sbcglobal.net>

Hi Albert Zweistein, thanks for writing this:
> I need one or two PL/SQL's that will let me
>
> - Disable ALL Constraints for one or more tables
>
> and at a later time
>
> - Enable ALL Constraints for one or more tables
>
>
> Please post any answers to the Newstgroup.
>
>
> Thanks - Albert !
>

I use the following when bulk loading data. Of course, I have to be ready to fix any constraint violations when constraints are re-enabled.

• ====================================================================
• Abstract of: prep_bulk_load.sql
• When bulk loading tables, we don't want to worry about being
• blocked from loading tables in any order we choose by referential
• integrity rules. This script is handy in this and other similar
• situations. --
• Generate a script (disable.sql) that will disable all enabled
• constraints and triggers for the current schema owner. At the
• same time, generate a script (enable.sql) that will enable all
• constraints and triggers disabled by disable.sql. --
• In summary, your steps should be as follows: --
• 1. Connect to sqlplus as the desired schema owner
• 2. Run this script to generate disable.sql and enable.sql
• 3. Run disable.sql to disable constraints and triggers
• 4. Do whatever tasks are required
• 5. run enable.sql to enable constraints and triggers
• ==================================================================== set pagesize 0 echo off feedback off trimspool on

spool disable.sql
select 'ALTER TABLE ' || lower(table_name) || chr(10) ||

       'DISABLE CONSTRAINT ' || lower(constraint_name) || ';' from user_constraints
where constraint_type = 'R'
and status = 'ENABLED'
/
select 'ALTER TRIGGER ' || lower(trigger_name) || ' DISABLE;' from user_triggers
where status = 'ENABLED'
/
spool off

spool enable.sql

select 'SPOOL enable.log' from dual;
select 'ALTER TABLE ' || lower(table_name) || chr(10) ||
       'ENABLE CONSTRAINT ' || lower(constraint_name) || ';'

from user_constraints
where constraint_type = 'R'
and status = 'ENABLED'
/
select 'ALTER TRIGGER ' || lower(trigger_name) || ' ENABLE;' from user_triggers
where status = 'ENABLED'
/
select 'SPOOL off' from dual;
spool off

set pagesize 24 echo on feedback on

-- 
[:%s/Karsten Farrell/Oracle DBA/g]

Received on Tue Jul 22 2003 - 11:24:06 CDT