RE: Disable/Enable scripts?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 23 Dec 2004 11:40:02 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87BA7@irvmbxw02>

As part of my answer I forgot to say that in 9.2 you can disable a primary key or unique constraint with the "keep index" option, e.g. alter table disable primary key keep index ; a lter table disable unique (col1, col2, col3) keep index ; alter table disable constraint table_pk_constraint_name keep index ;  

But if the enforcing index is a unique index, uniqueness of the rows will still be enforced despite the absence of the primary key or unique constraint.
On the other other hand, keeping the index will make re-enabling the constraint faster and easier.
-----Original Message-----
Bryan Wells

All, Im looking for a script to Disable and enable constraints. Is there an order? I would think the PK would need to be enabled first then the FK.

Answer:

Yes, you should first disable the FK constraints before the PK or Unique constraints, and enable them in the reverse order. Disabling is easy (see an example script below.)

When you re-enable a primary key or unique constraint do you want to recreate the enforcing index with the same storage parameters as it had before you disabled the constraint? That is a little trickier.

Disabling constraints in a certain order: define schema = "SPC_TBL_OWNER"
set linesize 132
set pagesize 0
set recsep off

column sort_id1 noprint
column sort_id2 noprint
column sort_id3 noprint
column sort_id4 noprint

 select

    1 as sort_id1,
    owner as sort_id2,
    table_name as sort_id3,
    constraint_name as sort_id4,
'alter table "' || owner || '"."' || table_name ||

       '"' || chr (10) || ' disable constraint "' || constraint_name || '" ;'

       as sql_text
  from all_constraints
  where

     (owner = '&schema' or r_owner = '&schema')
     and constraint_type = 'R'

union all
 select

    2 as sort_id1,
    owner as sort_id2,
    table_name as sort_id3,
    constraint_name as sort_id4,
'alter table "' || owner || '"."' || table_name ||

       '"' || chr (10) || ' disable constraint "' || constraint_name || '" ;'

       as sql_text
  from all_constraints
  where

     owner = '&schema'
     and constraint_type = 'C'

union all
 select

    3 as sort_id1,
    owner as sort_id2,
    table_name as sort_id3,
    constraint_name as sort_id4,
'alter table "' || owner || '"."' || table_name ||

       '"' || chr (10) || ' disable constraint "' || constraint_name || '" ;'

       as sql_text
  from all_constraints
  where

     owner = '&schema'
     and constraint_type in ('P', 'U')

order by
  1, 2, 3, 4 ;

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 23 2004 - 13:38:25 CST