Re: disabling and enabling all constraints

From: Olivier Bercovitz <oberco_at_club-internet.fr>
Date: Fri, 14 Aug 1998 22:43:43 +0200
Message-ID: <35D4A17F.DA78EEC5@club-internet.fr>

Hello David,

you can do it with a sqlplus script like this :

sql > set heading off
sql > set pagesize 0
sql > spool  my_tmp_file.sql
sql > select  'alter table ' || table_name || ' disable constraint ' || owner ||
'.' || constraint_name || ';'
        from dba_constraints where owner not in ('SYS', 'SYSTEM');

sql > @my_tmp_file.sql

In fact you create and execute a temporary sql file which contains order for desactivating all constraints
except for user sys and system
Replace disable by enable to reactivate the constraints Execute this script under a user with access privilege to dba_constraints view (like system)
or you can replace the view dba_constraints by the view user_constraints for a specific owner

Hope this help's

Regards
Olivier

David Chasteen wrote:

> I want to load data back into a recreated database without worrying about
> referential integrity. The data was validated when it was input and will
> not be changed before being run back into the database.
>
> Is there a simple command to disable all constraints in the database so I
> can run the data back in without concern for table load order?
>
> I can't seem to find a clear reference and have not been able to do anything
> other than constraints one at a time.
>
> Thank you,
>
> David
Received on Fri Aug 14 1998 - 15:43:43 CDT