RE: Script request for FK enable/disable

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Thu, 17 Aug 2000 15:15:01 -0400
Message-Id: <10592.114893@fatcity.com>

set heading off
set feedback off
set linesize 160
spool truncate_all.lst
select 'alter table ' || table_name || ' disable constraint ' ||

        constraint_name || ' cascade;'
from user_constraints
where constraint_type = 'R'
/

select 'truncate table ' || segment_name || ';' from user_tables
/

select 'alter table ' || table_name || ' enable constraint ' || constraint_name

        || ';'
from user_constraints
where status = 'DISABLED'
/

spool off
set heading on
set feedback on
set linesize 80
@truncate_all.lst

-----Original Message-----
From: Steven Monaghan [mailto:MonaghaS_at_mscdirect.com] Sent: Thursday, August 17, 2000 3:01 PM
To: Multiple recipients of list ORACLE-L Subject: Script request for FK enable/disable

I inherited a job that tries to truncate about 20 tables weekly. After executing the truncate command, the script then does a delete cascade of the table. This was done because some of the truncate commands fail due to:

ORA-02266: unique/primary keys in the table referenced by enabled foreign keys

Does anyone out there have a script already developed that will allow me to enter a table name and have it generate the appropriate disable and enable commands, so I can truncate the table?

I've been trying to figure it out, using the dba_constraints table, but I haven't gotten very far yet, and I'd like to avoid re-inventing the wheel if possible.

Thanks again to the list for your help with the issue I raised yesterday about redo logs on import. We are going to turn logging off on the tables before the load and turn it back on after the load. Hopefully that will put out one of the fires with this process.

Steve

---

Steve Monaghan
Oracle DBA
MSC Industrial Direct Co., Inc.
Melville, NY
MonaghaS_at_mscdirect.com

---

-- 
Author: Steven Monaghan
  INET: MonaghaS_at_mscdirect.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).

Received on Thu Aug 17 2000 - 14:15:01 CDT