Script – Disable FK Constraints Referencing a Table

Below is an Oracle SQL script to generate DISABLE and ENABLE commands for all foreign key constraints referencing a table. For more info, see Finding table dependencies (foreign key constraints) in Oracle – walking the dependency tree.

table_dep.sql

REM ###########################################################
REM Generates enable and disable constrains for all tables
REM with FK dependencies on the specified table.
REM USAGE: table_dep <owner> <name>
REM ###########################################################

SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF

COLUMN cmdpar1 NEW_VALUE table_owner NOPRINT
COLUMN cmdpar2 NEW_VALUE table_name NOPRINT

SET TERMOUT OFF
SELECT 
 UPPER( '&1' ) cmdpar1, 
 UPPER( '&2' ) cmdpar2
FROM dual;
SET TERMOUT ON

PROMPT #########################################
PROMPT DISABLE SCRIPTS
PROMPT #########################################
SELECT DISTINCT 'ALTER TABLE ' || c.owner || '.' || c.table_name || ' DISABLE CONSTRAINT ' || c.constraint_name || ';'
FROM dba_constraints r, dba_constraints c
WHERE r.owner = c.r_owner
 AND r.constraint_name = c.r_constraint_name
START WITH r.owner = '&TABLE_OWNER' AND r.table_name = '&TABLE_NAME'
CONNECT BY NOCYCLE r.owner = PRIOR c.owner
 AND r.table_name = PRIOR c.table_name;
 
PROMPT
PROMPT
 
PROMPT #########################################
PROMPT ENABLE SCRIPTS
PROMPT #########################################
SELECT DISTINCT 'ALTER TABLE ' || c.owner || '.' || c.table_name || ' ENABLE CONSTRAINT ' || c.constraint_name || ';'
FROM dba_constraints r, dba_constraints c
WHERE r.owner = c.r_owner
 AND r.constraint_name = c.r_constraint_name
START WITH r.owner = '&TABLE_OWNER' AND r.table_name = '&TABLE_NAME'
CONNECT BY NOCYCLE r.owner = PRIOR c.owner
 AND r.table_name = PRIOR c.table_name;


SET FEEDBACK ON
SET HEADING ON
SET VERIFY ON

UNDEFINE &table_owner
UNDEFINE &table_name
Advertisements