RE: Script request for FK enable/disable

From: Steven Monaghan <MonaghaS_at_mscdirect.com>
Date: Thu, 17 Aug 2000 14:25:10 -0400
Message-Id: <10592.114881@fatcity.com>

Thanks anyway list gurus, but I found some stuff on Metalink.

Below are the scripts I have created/hacked from Note:1039297.6:

disable_enable_FKs.sql
----- cut -----

rem **************************************************************** 

rem
rem If necessary, reformat the columns to avoid column wrapping. rem
rem Change the table name USER_CONSTRAINTS to ALL_CONSTRAINTS or rem DBA_CONSTRAINTS to change the scope of the query. rem
rem To list the reference on a particular table only, add the table rem name to the WHERE clause, i.e.,
rem
rem AND A.TABLE_NAME = &tbl_name
rem

rem **************************************************************** 
 

accept tbl_name prompt 'Enter the table name --> ' accept owner prompt 'Enter the owner --> '  

column table_name         format a30 
column key_name           format a30 

column referencing_table format a30
column foreign_key_name format a30
column fk_status format a8  

set linesize 140
set pagesize 100  

SELECT
    'alter table '||b.owner||'.'||b.table_name||' disable constraint '||B.CONSTRAINT_NAME||';'
  FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B   WHERE

    A.TABLE_NAME = upper('&tbl_name') AND
    A.OWNER = upper('&owner') AND
    B.OWNER = upper('&owner') AND
    A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME and 
    B.CONSTRAINT_TYPE = 'R' 

;  

SELECT
    'alter table '||b.owner||'.'||b.table_name||' enable constraint '||B.CONSTRAINT_NAME||';'
  FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B   WHERE

    A.TABLE_NAME = upper('&tbl_name') AND
    A.OWNER = upper('&owner') AND
    B.OWNER = upper('&owner') AND
    A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME and 
    B.CONSTRAINT_TYPE = 'R' 

;
----- cut -----

FKs_by_table.sql
----- cut -----

rem **************************************************************** 

rem
rem If necessary, reformat the columns to avoid column wrapping. rem
rem Change the table name USER_CONSTRAINTS to ALL_CONSTRAINTS or rem DBA_CONSTRAINTS to change the scope of the query. rem
rem To list the reference on a particular table only, add the table rem name to the WHERE clause, i.e.,
rem
rem AND A.TABLE_NAME = &tbl_name
rem

rem **************************************************************** 
 

accept tbl_name prompt 'Enter the table name --> ' accept owner prompt 'Enter the owner --> '  

column table_name         format a30 
column key_name           format a30 

column referencing_table format a30
column foreign_key_name format a30
column fk_status format a8  

set linesize 140
set pagesize 100  

SELECT

    A.TABLE_NAME table_name, 
    A.CONSTRAINT_NAME key_name, 
    B.TABLE_NAME referencing_table, 
    B.CONSTRAINT_NAME foreign_key_name, 
    B.STATUS fk_status  

  FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B   WHERE

    A.TABLE_NAME = '&tbl_name' AND
    A.OWNER = '&owner' AND
    B.OWNER = '&owner' AND
    A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME and 
    B.CONSTRAINT_TYPE = 'R' 

  ORDER BY 1, 2, 3, 4;
----- cut -----

> -----Original Message-----
> From: Steven Monaghan
> Sent: Thursday, August 17, 2000 1:55 PM
> To: Oracle List (E-mail)
> 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
Received on Thu Aug 17 2000 - 13:25:10 CDT