Re: Disabling Constraints in a Schema!..??

Arul

You can use the following generic script to disable all the primary key and the corresponding foreign key constraints.

set pages 0 feed off
spool fk_dis.sql
select 'alter table '||table_name
|| ' DISABLE constraint ' ||constraint_name ||' CASCADE '||';' from user_constraints
;
spool off

However be aware that when you Enable the Primary key constraints they don't go to the proper tablespaces instead they go to the user's default tablespaces. At least that is the case with 8.1.5.

So what I do for my datawarehouse load is to disable only the Foreign Keys , load the data and try to Re-Enable the foreign keys and write into the exceptions table if there are any.

Hope that helps.

Surjit

Arul kumar <rakumar_at_mahindrabt.com>@fatcity.com on 02/03/2001 19:30:45

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

Hi DBAs,

We r using Oracle 8.1.6 . db.
I would like to disable all the constraints on the Tables available under one schema.
Is there any single command to do this in 8i . Any DBMS package available?
Any other suggestions for doing the same??

Thank You.
Arul.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arul kumar
  INET: rakumar_at_mahindrabt.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Surjit Sharma
  INET: surjit_at_au1.ibm.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).