跨地區資料合併、代碼表批次更新、大量歷史資料遷移時,需暫停外鍵與觸發器避免約束檢查失敗與觸發器連鎖反應,作業完成後一鍵恢復。
P_ALTERCONSCREATE OR REPLACE PROCEDURE P_ALTERCONS (p_action IN VARCHAR2) AS
v_sql VARCHAR2(400);
v_fk_cnt NUMBER := 0;
v_trg_cnt NUMBER := 0;
BEGIN
IF UPPER(p_action) NOT IN ('ENABLE','DISABLE') THEN
RAISE_APPLICATION_ERROR(-20001, '參數必須為 ENABLE 或 DISABLE');
END IF;
DBMS_OUTPUT.PUT_LINE('START: '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
-- 1. 處理外鍵
FOR r IN (SELECT table_name, constraint_name
FROM user_constraints WHERE constraint_type = 'R') LOOP
v_sql := 'ALTER TABLE '||r.table_name||' '||UPPER(p_action)||
' CONSTRAINT '||r.constraint_name;
BEGIN
EXECUTE IMMEDIATE v_sql;
v_fk_cnt := v_fk_cnt + 1;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FK '||r.constraint_name||' '||p_action||' 失敗: '||SQLERRM);
END;
END LOOP;
-- 2. 處理觸發器
FOR r IN (SELECT trigger_name FROM user_triggers) LOOP
v_sql := 'ALTER TRIGGER '||r.trigger_name||' '||UPPER(p_action);
BEGIN
EXECUTE IMMEDIATE v_sql;
v_trg_cnt := v_trg_cnt + 1;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Trigger '||r.trigger_name||' '||p_action||' 失敗: '||SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('FK processed: '||v_fk_cnt||', Triggers processed: '||v_trg_cnt);
DBMS_OUTPUT.PUT_LINE('END: '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
END;
SET SERVEROUTPUT ON
EXEC P_ALTERCONS('DISABLE'); -- 停用所有 FK & Trigger
-- 執行資料合併/清洗/遷移
EXEC P_ALTERCONS('ENABLE'); -- 恢復所有 FK & Trigger
-- 當 INSERT/LOAD 報 ORA-02291 時,快速找出違規行
SELECT 'FK '||c.constraint_name||' 違規: '||
'子表 '||c.table_name||'('||cc.column_name||') 值 '||
'不存在於 父表 '||p.table_name||'('||pc.column_name||')'
FROM user_constraints c
JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
JOIN user_constraints p ON c.r_constraint_name = p.constraint_name
JOIN user_cons_columns pc ON p.constraint_name = pc.constraint_name
AND cc.position = pc.position
WHERE c.constraint_type = 'R'
AND c.table_name = UPPER('&child_table'); -- 輸入報錯的子表名
ANALYZE TABLE ... VALIDATE CONSTRAINT 驗證現有資料合規。DBMS_UTILITY.EXEC_DDL_STATEMENT 並行執行加速。