Wanted:PL/SQL to Disable & Enable Constraints

[Albert Zweistein]

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 !

[Karsten Farrell]

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]

[Burton Peltier]

Set up the contraint(s) as "deferrable" and with a simple "alter session"
command you can defer constraint checking until commit time for all tables .

--
"Albert Zweistein" <azwei@web.de> wrote in message
news:Xns93C0B854DF3A1azweiwebde@62.243.74.162...

> 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 !

[grjohnson]

BEGIN

DECLARE

FROM r_disable IN
(SELECT table_name, constaint_name
FROM user_constraints
WHERE constraint_type = 'R'
AND status = 'ENABLED') LOOP
execute immediate 'ALTER TABLE ' || r_disable.table_name 'DISABLE
CONSTRAINT ' || r_disable.constraint_name;
END LOOP;

END;

Cheers

Greg Johnson

> --
> "Albert Zweistein" <azwei@web.de> wrote in message
> news:Xns93C0B854DF3A1azweiwebde@62.243.74.162...

> > 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 !