作為一個Oracle數據庫管理員，會碰到這樣的數據庫管理需求，停止或者打開當前用戶（模式）下所有表的約束條件和觸發器。這在數據庫的合並以及對數據庫系統的代碼表中某些代碼的修改時需要做的工作之一。
我們來看這樣一種實際數據庫工作業務需求，這在目前的許多應用中是非常實際的。某地區銀行數據，目前采用市級數據集中，隨著計算機網絡技術的不斷提高以及對服務水平的要求，提出了省級乃至國家級的數據集中。除了應用需要修改以外，對於數據庫管理員來講，最重要的工作就是 對各地分散管理的數據庫統一集中到一個或者幾個集中數據庫中。此時就需要整理以前各地各自為政的 代碼表為一個統一的代碼表以及數據庫的最後集中合並。
對Oracle數據庫管理員來講，這樣的數據維護工作，在更新代碼表中代碼或者合並數據之前，首先要作的工作就是將系統中某用戶下所有的外鍵或觸發器停止，處理完數據後，再打開這些關閉的外鍵和觸發器。針對這樣的工作需求，本文給出了下面兩個SQL腳本：(1)系統中某模式或用戶下外鍵或者觸發器的管理腳本；(2)外鍵錯誤自動查找腳本 。下面就來詳細介紹這兩個腳本。
一、約束管理腳本
該腳本可用來管理當前登錄用戶下的所有外鍵和觸發器的打開和關閉，此處沒有處理主鍵和唯一約束條件，該腳本稍加修改就可以處理主鍵和唯一約束條件，但這裡建議最好不要在隨意停止主鍵或唯一約束條件後，進行數據維護。
腳 本運行方法如下(SQL/PLUS)：
SQL> SET SERVEROUTPUT ON;
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SQL> EXEC P_ALTERCONS (as_alter);
其中，參數as_alter只能是“ENABLE”或者 “DISABLE”，否則程序提示錯誤。當參數為“ENABLE”時，表示將當前模式下所 有的外鍵和觸發器打開，相反“DISABLE”就是將當前模式下所有的外鍵和觸發器關閉。
附存儲過程腳本：
CREATE OR REPLACE PROCEDURE P_ALTERCONS(
AS_ALTER VARCHAR2)
AS
v_CursorID INTEGER;
v_StrCon VARCHAR2(300);
v_StrTri VARCHAR2(300);
v_FkNum NUMBER :=0;
v_TriNum NUMBER :=0;
v_sqlcode NUMBER;
v_sqlerrm VARCHAR2(600);
CURSOR C_CON IS
SELECT * FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE='R';
R_CON C_CON%ROWTYPE;
CURSOR c_trigger IS
SELECT TRIGGER_NAME,STATUS FROM user_triggers;
notfound BOOLEAN;
BEGIN
DBMS_OUTPUT.PUT_LINE('BEGIN TIME: '||to_char(sysdate));
判斷輸入參數是否 為DISABLE或者是ENABLE，如果是的話，就繼續處理，否則退出過程，給出提示
IF (UPPER (AS_ALTER) = 'DISABLE' OR UPPER(AS_ALTER) = 'ENABLE') THEN
OPEN C_CON;
當前用戶下外鍵的處理ENABLE或者DISABLE
v_CursorID := DBMS_SQL.OPEN_CURSOR;
LOOP
FETCH C_CON into R_CON;
notfound:=C_CON% NOTFOUND;
EXIT WHEN notfound;
begin
IF (UPPER(AS_ALTER)='DISABLE' AND R_CON.STATUS='ENABLED' OR
UPPER(AS_ALTER)='ENABLE' AND R_CON.STATUS='DISABLED') THEN
v_StrCon := 'ALTER TABLE '||R_CON.owner||'.'||R_CON.table_name||' '||
UPPER(as_alter) || ' CONSTRAINT '||R_CON.constraint_name;
DBMS_SQL.PARSE( v_CursorID, v_StrCon, DBMS_SQL.V7);
v_FkNum :=v_FkNum+1;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SUBSTR(SQLERRM,1,600);
DBMS_OUTPUT.PUT_LINE('ERROR: '||' '||V_SQLERRM);
-- 找出錯誤原因
IF (v_sqlcode = -2298) THEN
p_con_err(R_CON.CONSTRAINT_NAME);
END IF;
END;
END LOOP;
CLOSE C_CON;
DBMS_OUTPUT.PUT_LINE('====== Foreign Keys were '||as_alter||', total '||to_char(v_FkNum)||' =====');
-- 當前 用戶下觸發器的處理 ENABLE或者 DISABLE
FOR T_TRIGGER IN C_TRIGGER LOOP
BEGIN
IF (UPPER(AS_ALTER)='DISABLE' AND T_TRIGGER.STATUS='ENABLED' OR
UPPER(AS_ALTER)='ENABLE' AND T_TRIGGER.STATUS='DISABLED') THEN
v_StrTri := 'ALTER TRIGGER '||T_TRIGGER.TRIGGER_name ||' '||UPPER (as_alter);
DBMS_SQL.PARSE( v_CursorID, v_StrTri, DBMS_SQL.V7);
v_TriNum :=v_TriNum+1;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SUBSTR(SQLERRM,1,600);
DBMS_OUTPUT.PUT_LINE('ERROR: '||V_SQLCODE||' '||V_SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('====== Triggers were '||as_alter||', total '||to_char(v_TriNum)||' =====');
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
ELSE -- 輸入參數不正確
DBMS_OUTPUT.PUT_LINE('ERROR:輸入參數不正確，參數為ENABLE 或者DISABLE!');
END IF;
DBMS_OUTPUT.PUT_LINE('END TIME: '||to_char (sysdate));
END;
/
二、約束錯誤自動查找腳本
一般，數據庫管理 員在對數據進行維護時，如新數據的導入前，首先要關閉所有的外鍵和觸發器，數據成功導入後，再打 開導入前關閉的外鍵和觸發器。這時經常會遇到錯誤號為ORA-02298的“未找到父項關鍵字” 的錯誤。該錯誤的原因就是數據庫表中出現了不能滿足外鍵約束條件的記錄。這裡，另外給出了一個腳 本（P_CON_ERR）用來自動查找造成這類錯誤的原因，也就是找出不滿足外鍵約束條件的字段值。
該存儲過程可單獨運行，同時在前面介紹的存儲過程P_ALTERCONS中也進行了調用，在存儲過程 P_ALTERCONS中，可以看到在打開外鍵時，如果出現錯誤號為ORA-02298的錯誤，就調用該存儲過程，自 動查找造成外鍵不能啟動的原因。
下面是單獨運行該存儲過程的例子，在SQL/PLUS環境下：
SQL> SET SERVEROUTPUT ON;
SQL> EXEC P_CON_ERR ('FK_SB_HJJL_RELATION__SB_PZXH');
外鍵錯誤情況
============
NSRNM,PZLXDM,PZXH
-----------------
190321058, 900, 13546
777777775, 108, 17526
777777775, 108, 17528
777777775, 108, 17531
777777775, 108, 17546
PL/SQL過程已成功完成。
其中，FK_SB_HJJL_RELATION__SB_PZXH為出現錯 誤的外鍵名稱。
附存儲過程腳本：
CREATE OR REPLACE PROCEDURE P_CON_ERR (as_constraint_name varchar2)
AS
v_CursorID INTEGER;
V_CONSNAME VARCHAR2 (30);
V_TABLE_NAME VARCHAR2(30);
V_RTABLE_NAME VARCHAR2(30);
V_COLUMN VARCHAR2(100);
v_Str VARCHAR2(600);
TYPE t_col_value IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
v_Col_Val t_col_value;
v_RET NUMBER;
v_NUM NUMBER;
i BINARY_INTEGER;
V_WHERE VARCHAR2(600);
V_CAUSE VARCHAR2(200);
CURSOR C_COL_NAME(V_CON_NAME VARCHAR2) IS
SELECT * FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME=V_CON_NAME;
BEGIN
V_CONSNAME := TRIM(UPPER(as_constraint_name)); -- 約束名稱
v_num :=0;
FOR T_COL_NAME IN C_COL_NAME(V_CONSNAME) LOOP
IF (V_NUM = 0) THEN
V_COLUMN :=T_COL_NAME.COLUMN_NAME;
V_WHERE :='A.'||T_COL_NAME.COLUMN_NAME||'= B.'||T_COL_NAME.COLUMN_NAME;
ELSE
V_COLUMN :=V_COLUMN||','||T_COL_NAME.COLUMN_NAME;
V_WHERE :=V_WHERE||' AND '||'A.'||T_COL_NAME.COLUMN_NAME||'= B.'||T_COL_NAME.COLUMN_NAME;
END IF;
V_NUM :=V_NUM+1;
END LOOP;
FOR I IN 1..V_NUM LOOP
V_COL_VAL(I) :='';
END LOOP;
SELECT DISTINCT TABLE_NAME INTO V_TABLE_NAME FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME = V_CONSNAME;
-- 找到被引用的表名稱
SELECT TABLE_NAME INTO V_RTABLE_NAME FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME IN (SELECT R_CONSTRAINT_NAME FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = V_CONSNAME);
DBMS_OUTPUT.PUT_LINE(' 外鍵錯誤情況');
DBMS_OUTPUT.PUT_LINE('============');
DBMS_OUTPUT.PUT_LINE(v_column);
DBMS_OUTPUT.PUT_LINE(RPAD('-',LENGTH (V_COLUMN),'-'));
v_CursorID := DBMS_SQL.OPEN_CURSOR;
V_STR := 'SELECT DISTINCT '||V_COLUMN||' FROM '||V_TABLE_NAME||' A WHERE NOT EXISTS ( SELECT NULL FROM '
||V_RTABLE_NAME||' B WHERE '||V_WHERE||') ';
DBMS_SQL.PARSE( v_CursorID, v_Str, DBMS_SQL.V7);
FOR I IN 1..V_NUM LOOP
DBMS_SQL.DEFINE_COLUMN(v_CursorID,I,v_COL_VAL(I),30);
END LOOP;
v_ret := DBMS_SQL.EXECUTE(v_CursorID);
WHILE DBMS_SQL.FETCH_ROWS(v_CursorID) > 0 LOOP
V_CAUSE :='';
FOR I IN 1..V_NUM LOOP
DBMS_SQL.COLUMN_VALUE (v_CursorID,I,V_COL_VAL(I));
IF (I = 1) THEN
V_CAUSE :=V_COL_VAL(I);
ELSE
V_CAUSE :=V_CAUSE||', '||V_COL_VAL(I);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_CAUSE);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
END;
/
