Oracle Snapshot + DB Link 雙向同步實作：從單向到雙向的完整腳本 利用 Oracle Snapshot 與 DB Link 實現資料表同步：單向與雙向完整實作

採用Materialized View (Snapshot) + DB Link + Trigger 架構，可實現低延遲、可控的資料表同步，適用於異構或同構環境。

核心物件對照

• Source：被同步端（資料來源）。
• Target：同步目的端。

前置 6 步驟（單向同步必做）

1. Target 建立 DB Link：CREATE PUBLIC DATABASE LINK dblink_src CONNECT TO usr IDENTIFIED BY pwd USING 'src_tns';
2. 雙端建立同步表（建議有 PK）：CREATE TABLE test_user (id NUMBER(10) PRIMARY KEY, name VARCHAR2(12), age NUMBER(3));
3. Target 測試 DB Link：SELECT * FROM test_user@dblink_src;
4. Source 建立 MV Log：CREATE MATERIALIZED VIEW LOG ON test_user;
5. Target 建立 Materialized View：CREATE MATERIALIZED VIEW mv_test_user AS SELECT * FROM test_user@dblink_src;
6. 設定刷新模式（擇一）：
   • 快速刷新（需 MV Log + PK）：ALTER MATERIALIZED VIEW mv_test_user REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/1440 WITH PRIMARY KEY;（每分鐘）
   • 完整刷新：ALTER MATERIALIZED VIEW mv_test_user REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1/1440;

手動刷新指令

BEGIN DBMS_MVIEW.REFRESH('mv_test_user', 'F'); END;  -- F=Fast, C=Complete
EXEC DBMS_SNAPSHOT.REFRESH('mv_test_user','F');

單向同步 Trigger（Target 端）

CREATE OR REPLACE TRIGGER trg_mv_test_user_afr
AFTER INSERT OR UPDATE OR DELETE ON mv_test_user
FOR EACH ROW
BEGIN
  IF DELETING THEN
    DELETE FROM test_user WHERE id = :OLD.id;
  ELSIF INSERTING THEN
    INSERT INTO test_user (id, name, age) VALUES (:NEW.id, :NEW.name, :NEW.age);
  ELSIF UPDATING THEN
    UPDATE test_user SET name = :NEW.name, age = :NEW.age WHERE id = :OLD.id;
  END IF;
END;
/

雙向同步關鍵：防止 Trigger 死循環

Source 端同樣建立 MV + Trigger，但在 Trigger 中必須判斷資料是否真正異動，避免 Target 刷新 → 觸發 Source Trigger → 回寫 Target → 再刷新…無限循環。

CREATE OR REPLACE TRIGGER trg_test_user_bidir
AFTER INSERT OR UPDATE OR DELETE ON test_user
FOR EACH ROW
DECLARE
  v_cnt NUMBER;
BEGIN
  IF INSERTING THEN
    SELECT COUNT(*) INTO v_cnt FROM test_user@dblink_tgt WHERE id = :NEW.id;
    IF v_cnt = 0 THEN
      INSERT INTO test_user@dblink_tgt (id, name, age) VALUES (:NEW.id, :NEW.name, :NEW.age);
    END IF;
  ELSIF UPDATING THEN
    SELECT COUNT(*) INTO v_cnt FROM test_user@dblink_tgt
    WHERE id = :OLD.id AND (name != :NEW.name OR age != :NEW.age);
    IF v_cnt > 0 THEN
      UPDATE test_user@dblink_tgt SET name = :NEW.name, age = :NEW.age WHERE id = :OLD.id;
    END IF;
  ELSIF DELETING THEN
    DELETE FROM test_user@dblink_tgt WHERE id = :OLD.id;
  END IF;
END;
/

刷新間隔時間換算備忘

監控視圖

• ALL_SNAPSHOT_REFRESH_TIMES：最後刷新時間。
• USER_JOBS：下次排程時間（NEXT_DATE）。

Materialized View 同步架構簡單、無需額外授權、支援快速刷新，是中小規模異地同步的首選方案。