RE: Disable/Enable scripts?
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <bunjibry@xxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Dec 2004 11:40:02 -0800
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 ;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
