Oracle 數據同步全方案：DBLink + 物化視圖 + Job 調度

三層架構：連結 → 捕獲 → 刷新

第一層：建立 DBLink (異構/同構通用)

-- 1. 刪除舊連結
DROP DATABASE LINK dblink1;

-- 2. 建立連結 (完整 TNS 描述符，避免依賴 tnsnames.ora)
CREATE DATABASE LINK dblink1
CONNECT TO csys IDENTIFIED BY "****"
USING '(DESCRIPTION =
  (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.134)(PORT = 1521)))
  (CONNECT_DATA = (SERVICE_NAME = orcl))
)';

-- 3. 驗證
SELECT * FROM codevalue@dblink1 WHERE ROWNUM < 5;

-- 4. 查看現有 DBLink
SELECT owner, object_name FROM dba_objects WHERE object_type = 'DATABASE LINK';

客戶端 sqlnet.ora 補充：若服務器無 tnsnames.ora，可在 $ORACLE_HOME/NETWORK/ADMIN/sqlnet.ora 加入 NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT) 支援 Easy Connect。

第二層：物化視圖日誌 (MV Log) - 捕獲增量

-- 主表有主鍵
CREATE MATERIALIZED VIEW LOG ON csb1 WITH PRIMARY KEY;

-- 主表無主鍵 (用 ROWID)
CREATE MATERIALIZED VIEW LOG ON csb1 WITH ROWID;

-- 查看日誌表
SELECT * FROM MLOG$_CSB1;

第三層：建立物化視圖 + 定時刷新

方式 A：內建調度 (START WITH ... NEXT)

-- 有主鍵，FAST REFRESH 每 10 分鐘
CREATE MATERIALIZED VIEW mv_csb1
REFRESH FAST
START WITH SYSDATE
NEXT TRUNC(SYSDATE,'MI') + 10/1440  -- 10 分鐘 = 10/1440 天
AS SELECT * FROM csb1@dblink1;

-- 無主鍵，需 WITH ROWID
CREATE MATERIALIZED VIEW mv_csb1
REFRESH FAST WITH ROWID
START WITH SYSDATE
NEXT TRUNC(SYSDATE,'MI') + 10/1440
AS SELECT * FROM csb1@dblink1;

方式 B：DBMS_JOB 手動調度 (靈活控制)

-- 1. 建立刷新存儲過程
CREATE OR REPLACE PROCEDURE refresh_mv AS
BEGIN
  DBMS_MVIEW.REFRESH('MV_CSB1', 'F'); -- F=FAST, C=COMPLETE, ?=FORCE
END refresh_mv;
/

-- 2. 提交 Job (每 10 分鐘)
DECLARE
  v_job NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job       => v_job,
    what      => 'BEGIN refresh_mv; END;',
    next_date => TRUNC(SYSDATE,'MI') + 10/1440,
    interval  => 'TRUNC(SYSDATE,''MI'') + 10/1440'
  );
  COMMIT;
END;
/

-- 3. 查看 Job
SELECT job, what, next_date, interval, failures FROM dba_jobs;

進階選項

• 完整刷新 (COMPLETE)：初始化或結構變更後，REFRESH COMPLETE。
• 強制刷新 (FORCE)：嘗試 FAST 失敗自動轉 COMPLETE，REFRESH FORCE。
• 並行刷新：DBMS_MVIEW.REFRESH('MV_CSB1', 'F', parallelism => 4);
• 分區表同步：大表按時間分區，MV 同步分區裁剪，僅刷新活躍分區。

監控與排錯

-- 查看 MV 狀態
SELECT mview_name, refresh_mode, refresh_method, last_refresh_date, staleness
FROM user_mviews;

-- 查看刷新歷史
SELECT * FROM dba_mview_refresh_times WHERE mview_name = 'MV_CSB1';