RE: Disable/Enable scripts?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 23 Dec 2004 11:33:44 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87BA6@irvmbxw02>

-----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:33:12 CST