Re: disabling and enabling all constraints

From: <johnvue_at_gte.net>
Date: Wed, 19 Aug 1998 00:00:06 GMT
Message-ID: <6rd4i6$ile$1@nnrp1.dejanews.com>

Your way WILL work. By putting the exact STORAGE clause with the ALTER TABLE ENABLE CONSTRAINT, we'll get the results we want. However, the STORAGE clause and TABLESPACE clause was not in Olivier's original message.

 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

Besides, generating the correct ALTER TABLE ENABLE CONSTRAINT with the correct STORAGE clause is a lot harder than most people think! Please see my more complete message on this subject I just posted today.

In article <6r854i$lku_at_sjx-ixn6.ix.netcom.com>,   "Winnie Liu" <poohland_at_hotmail.com> wrote:
> I think I have the same question on my head. why enable all the constraints
> later (after disable it) will not work with the storage clause that are used
> to created the constraint (mostly the primary key and unique key index)?
>
> Thanks
>
> Winnie
>
> Olivier Bercovitz wrote in message <35D6EE56.96E1197_at_club-internet.fr>...
> >Hello John,
> >
> >Why this script won't work for constraint with associated index ??
> >because of the order of the constraint to disable ??
> >
> >thanks for the answer
> >
> >
> >johnvue_at_gte.net wrote:
> >
> >> 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.
> >>
> >>
> >
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Aug 18 1998 - 19:00:06 CDT