Re: disabling and enabling all constraints

Just remember though, if any constraint was made using a STORAGE clause to create the associated index in another tablespace or change other parameters, this "simple" script won't work.

On Fri, 14 Aug 1998 22:43:43 +0200, Olivier Bercovitz <oberco_at_club-internet.fr> wrote:

>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 - 17:40:25 CDT