Re: disabling and enabling all constraints

Please, don't just tell us that 'the $400 RevealNet product is not 100% correct'.
Please, tell us what to watch for.

johnvue_at_gte.net wrote:

> First off, it is my opinion that disabling and re-enabling
> constraints is one of the most dangerous and error prone
> activities you can do in Oracle. With the following
> explanation, you'll see why.
>
> You are correct that the ordering of disable/enable
> constraints matters but there's also another little known
> caveat to the simplistic:
> select 'alter table enable constraint ... '
> from dba_constraints;
>
> Let's create a table that exists in tablespace "ts_db1".
> We want to have the "contact_id" column as the primary key.
> We know that primary key constraints are enforced with a
> unique index. Since it's really indexes we're dealing
> with, we can change their storage parameters like any other
> standard index. I want this "index" to reside in
> tablespace "ts_idx1" and NOT in the user's DEFAULT
> tablespace and NOT in the same tablespace as the table
> "ts_db1".
>
> Here's an example SQL statement for such table:
>
> create table contact_t
> (
> contact_id number(7),
> fname varchar(30),
> constraint cntct_contact_id_pk primary key (contact_id)
> using index tablespace ts_idx1
> storage (initial 128K next 128K minextents 1 pctincrease 0)
> )
> tablespace ts_db1
> storage (initial 128K next 128K minextents 1 pctincrease 0);
>
> THIS IS THE KEY POINT: The custom storage parameters and
> custom target tablespace of this constraint generated
> "index" is STORED WITH THE INDEX (in DBA_INDEXES).
>
> THIS IS THE KEY POINT REWORDED: The custom storage
> settings are NOT STORED WITH THE CONSTRAINT.
>
> If we DISABLE or DROP the constraint using:
>
> alter table disable constraint cntct_contact_id_pk;
> ... or ..
> alter table drop constraint cntct_contact_id_pk;
>
> ... the index called "cntct_contact_id_pk" gets dropped.
>
> Hence, disabling/dropping the constraint means dropping the
> index which then means losing the index's custom storage
> settings.
>
> If you ENABLE the constraint again with ...
>
> alter table enable constraint cntct_contact_id_pk;
>
> ... Oracle will rebuild the behind-the-scenes index but it
> won't build it the original way we intended. The index now
> resides in the users's DEFAULT tablespace and has the
> underlying tablespace's default storage parameters!
>
> Sometimes you can get away with the simplistic:
> select 'alter table enable constraint ... '
> from dba_constraints;
> ... type approach because developers don't bother
> customizing primary key index storage parameters. They'll
> go through the trouble of customizing free-standing
> indexes' parameters but not the indexes generated from
> primary keys.
>
> What's the solution then?
>
> You can write a more sophisticated & complex script that
> joins DBA_CONSTRAINTS to DBA_INDEXES to get the storage
> parameters. This is a little harder than it looks because
> it's possible for an index to support a constraint but
> not have the same name as the constraint! To pursue
> this even further, you'd also have to match up
> DBA_CONS_COLUMNS to DBA_IND_COLUMNS to find the indexes
> that don't have identical names to the constraint. In
> essence, you'd have to query 4 tables to properly
> generate a bulletproof 'alter table enable constraint ...;'
> command. A lot of work.
>
> There's more to the relationship between constraints and
> indexes in Oracle than most DBAs are aware of. I don't
> know of any books that talk about this particular subject
> to great depth. Unfortunately, there are countless scripts
> on different websites and books that show the simplistic
> approach but we know there's a lot more to it than that!
> Even the seemingly complete constraint script that comes
> with the $400 RevealNet product is not 100% correct!
>
> In article <35D6EE56.96E1197_at_club-internet.fr>,
> oberco_at_club-internet.fr wrote:
> > 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 - 20:28:02 CDT