Re: disabling and enabling all constraints

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 - 18:52:34 CDT