drop_fks_ref_table.sql

oracle dba 常用的管理脚本, 覆盖日常的系统管理.

--* File Name    : Drop_Fks_Ref_Table.sql
--* Author       : DR Timothy S Hall
--* Description  : Creates the DDL to drop the foreign keys that referenece the specified table.
--* Call Syntax  : @Drop_Fks_Ref_Table (table-name) (schema)
--* Last Modified: 28/01/2001
SET SERVEROUTPUT ON
SET LINESIZE 100
SET VERIFY OFF
SET FEEDBACK OFF
PROMPT

DECLARE

    CURSOR cu_fks IS
        SELECT *
        FROM   all_constraints a
        WHERE  a.owner      = Upper('&&2')
        AND    a.constraint_type = 'R'
        AND    a.r_constraint_name IN (SELECT a1.constraint_name
                                       FROM   all_constraints a1
                                       WHERE  a1.table_name = Upper('&&1')
                                       AND    a1.owner      = Upper('&&2'));

BEGIN

    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Droping Foreign Keys to ' || Upper('&&1'));
    FOR cur_rec IN cu_fks LOOP
        DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP CONSTRAINT ' || Lower(cur_rec.constraint_name) || ';');
    END LOOP; 

END;
/

PROMPT
SET VERIFY ON
SET FEEDBACK ON