| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> FW: Script request for FK enable/disable
Steve,
Here is my script that will build a sql that will disable your foreign key constraint. You need to change the owner name to your schema owner. After you ran this script, then you will be able to truncate the tables. After the tables are truncated, you can modified this script to enable the constraint.
bash-2.03$ cat build_disable_constraint.sql
spool disable_table_constraint.sql
set heading off
set echo off
set pagesize 10000
select 'alter table '|| owner || '.' || table_name || ' disable constraint
'|| constraint_name || ' ;'
from sys.dba_constraints
where constraint_type ='R' and owner in
('ATGADM','ATGCAT1','ATGCAT2','ATGCOM','ATGPLOG','DPS_PROFILE');
spool off
Hope this help.
Regards,
Lucia
-----Original Message-----
From: MonaghaS_at_mscdirect.com [mailto:MonaghaS_at_mscdirect.com]
Sent: Thursday, August 17, 2000 12:01 PM
To: ORACLE-L_at_fatcity.com
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
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |