萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 約束管理自動化腳本:一鍵啟用/停用全 Schema 外鍵與觸發器

業務場景

跨地區資料合併、代碼表批次更新、大量歷史資料遷移時,需暫停外鍵與觸發器避免約束檢查失敗與觸發器連鎖反應,作業完成後一鍵恢復。

核心程序 P_ALTERCONS

CREATE 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');  -- 輸入報錯的子表名

注意事項

  • 僅處理 **FK (R)** 與 **Trigger**,**不含 PK/UK/Check**,避免資料完整性風險。
  • 大量 FK 時執行時間較長,請在低峰窗口運行。
  • 啟用後建議跑 ANALYZE TABLE ... VALIDATE CONSTRAINT 驗證現有資料合規。
  • 12c+ 可改用 DBMS_UTILITY.EXEC_DDL_STATEMENT 並行執行加速。
copyright © 萬盛學電腦網 all rights reserved