RE: Disable/Enable scripts?
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <bunjibry@xxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Dec 2004 11:33:44 -0800
-----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:
