RE: Script request for FK enable/disable

From: Channa, Santhosh, SITS <schanna_at_att.com>
Date: Thu, 17 Aug 2000 16:20:17 -0400
Message-Id: <10592.114902@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01C00888.8F2A4360
Content-Type: text/plain

Hope these scripts will help you. go thru readme.txt first.

<<CreateDropRiScript.sql>> <<README.txt>> <<CreateAddRiScript.sql>>

> ----------
> From: Steven Monaghan[SMTP:MonaghaS_at_mscdirect.com]
> Sent: Thursday, August 17, 2000 3:00 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).
>

------_=_NextPart_000_01C00888.8F2A4360
Content-Type: application/octet-stream;

name="CreateDropRiScript.sql"
Content-Disposition: attachment;

filename="CreateDropRiScript.sql"

set pages 0
set head off
set pau off
set serveroutput on size 10000
set verify off
prompt

prompt     /********************************************************************************
prompt      ***  This will create a file called DropRiScript.sql under c:\temp\ directory ***
prompt      ********************************************************************************/

prompt
prompt Enter the table name which u want to drop : accept tname
spool c:\temp\DropRiScript.sql
select 'alter table '||b.table_name||' drop constraint '||b.constraint_name||' ;' from all_constraints a, all_constraints b

 where a.owner='IBSDBA'
   and a.constraint_name = b.r_constraint_name 
   and a.table_name = upper('&TName')
   and a.constraint_type in ('P','U')

/
spool
spool off
set pages 40
set head on
set pau on
------_=_NextPart_000_01C00888.8F2A4360
Content-Type: text/plain;

name="README.txt"
Content-Disposition: attachment;

filename="README.txt"

Create a temp table for which ur trying 2 drop. SQL> create table temp_tablename as select *from tablename;
Before going to drop a table first run "CreateDropRiScript.sql". This will create a file called "DropRiScript.sql" under "c:\temp\".
Run "CreateAddRiScripts.sql". This will create a file called "AddRiScript.sql" under "c:\temp\".
Now run "C:\temp\DropRiScript.sql" -- This will drop all RI's basically all child constraints.
Now run the ddl script which has the table modifications. ex. If you are adding a new column to client table then you have to run CLIENT.SQL. This ddl script will actually drops the existing table and recreates.
After creating this table copy all the data back from the temp table created at first step.
After adding the data into this table, run "C:\temp\AddRiScript.sql" -- This will Add all RI's back.
That's all your database changes have been done. Now you can drop the temp table created at first step.

------_=_NextPart_000_01C00888.8F2A4360
Content-Type: application/octet-stream;

name="CreateAddRiScript.sql"
Content-Disposition: attachment;

filename="CreateAddRiScript.sql"

set serveroutput on size 10000
set verify off
prompt

prompt     /********************************************************************************
prompt      ***  This will create a file called AddRiScript.sql under c:\temp\ directory ***
prompt      ********************************************************************************/

prompt
prompt Enter the table name which u want to drop : accept tname
spool c:\temp\AddRiScript.sql

Declare
pk_cons_name varchar2(100);
pk_tab varchar2(30);
refcols varchar2(100);
fkeys varchar2(100);
drule varchar2(20);

cursor pk_or_uk is
select constraint_name main_cons_name, table_name -- into pk_cons_name, pk_tab

from all_constraints
where owner = 'IBSDBA'

     and constraint_type in ('P', 'U')
     and table_name = upper('&TName');

cursor fk_cur(cons_name varchar2) is
select table_name, constraint_name from all_constraints where owner = 'IBSDBA'
and r_constraint_name = cons_name;

cursor cols(cons_name varchar2) is
  select column_name
    from all_cons_columns
   where constraint_name = cons_name
   order by position;

Begin

for main in pk_or_uk loop

for pkcols in cols(main.main_cons_name) loop refcols := refcols||pkcols.column_name||','; end loop;

refcols := substr(refcols,1,length(refcols)-1);

for fk in fk_cur(main.main_cons_name) loop

for fkcols in cols(fk.constraint_name) loop fkeys := fkeys||fkcols.column_name||','; end loop;

fkeys := substr(fkeys,1,length(fkeys)-1); select decode(delete_rule,'CASCADE',' ON DELETE CASCADE ',null)

      into drule
      from all_constraints 
     where constraint_name = fk.constraint_name;

    dbms_output.put_line('Alter table '||fk.table_name||' add constraint '||
	fk.constraint_name||' foreign key ( '||fkeys||' ) references '||
        main.table_name||'( '||refcols||' ) '||drule||';');

Received on Thu Aug 17 2000 - 15:20:17 CDT