Oracle 物化視圖實現表同步的完整指南

Oracle 物化視圖實現表同步的完整指南

表同步是資料庫管理中常見的需求，Oracle 物化視圖提供了強大的同步機制，支援同一資料庫內或跨資料庫（透過 Database Link）的資料同步。針對同庫同步，建議採用即時提交刷新模式；若使用 DB Link，則推薦採用增量快速刷新，避免對源資料庫造成效能衝擊。

核心實作步驟

1. 在源表建立物化視圖日誌
2. 建立目標表結構
3. 基於預建表建立物化視圖

實戰範例：即時同步模式

-- 1. 建立源表與物化視圖日誌
CONN bre/bre
CREATE TABLE t1 (id INT, name VARCHAR2(30));
ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (id) USING INDEX;
CREATE MATERIALIZED VIEW LOG ON t1 WITH PRIMARY KEY;

-- 2. 建立目標表與物化視圖
CREATE TABLE t2 AS SELECT * FROM t1 WHERE 1=2;
CREATE MATERIALIZED VIEW t2 ON PREBUILT TABLE 
  REFRESH FAST ON COMMIT AS SELECT * FROM t1;

同步驗證測試

-- 插入資料並提交
INSERT INTO t1 VALUES (1, 'A');
COMMIT;
SELECT * FROM t2;
-- 結果：即時同步成功
ID NAME
---------- ------------------------------
1 A

DDL 變更的限制與應對

物化視圖不支援 DDL 自動同步。當源表執行 ALTER TABLE ADD COLUMN 或 RENAME COLUMN 後，物化視圖會進入 COMPILATION_ERROR 狀態，同步中斷。

-- DDL 測試
ALTER TABLE t1 ADD sf INT;
ALTER TABLE t1 RENAME COLUMN name TO names;
-- 此時查詢 t2 發現新欄位 sf 未同步，舊欄位 name 未重命名

-- 檢視物化視圖狀態
SELECT STALENESS FROM user_mviews;
-- 結果：COMPILATION_ERROR


解決方案：重建為 ON DEMAND 模式
將物化視圖改為手動刷新模式，並透過完整刷新重建同步：
-- 重建物化視圖
CREATE MATERIALIZED VIEW t2 ON PREBUILT TABLE 
  REFRESH FAST ON DEMAND AS SELECT * FROM t1;

-- 手動完整刷新
EXEC DBMS_MVIEW.REFRESH('T2', 'C');
-- 後續增量刷新
EXEC DBMS_MVIEW.REFRESH('T2', 'F');

關鍵語法參考
建立物化視圖完整語法：
CREATE MATERIALIZED VIEW [schema.]mv_name
  [ON PREBUILT TABLE [WITH | WITHOUT REDUCED PRECISION]]
  REFRESH [FAST | COMPLETE | FORCE] 
  [ON COMMIT | ON DEMAND]
  [WITH PRIMARY KEY | ROWID]
  AS subquery;

建立物化視圖日誌語法：
CREATE MATERIALIZED VIEW LOG ON [schema.]table
  [WITH [PRIMARY KEY | ROWID | SEQUENCE | COMMIT SCN] [...]
  (column [, column]...)]
  [NEW VALUES [INCLUDING | EXCLUDING]];




最佳實踐建議

同庫同步優先使用 REFRESH FAST ON COMMIT 達成即時一致性
跨庫同步務必使用 REFRESH FAST ON DEMAND 搭配排程作業，避免 DB Link 長連接拖垮源庫
源表若頻繁 DDL 變更，建議改用 GoldenGate 或自訂 ETL 方案
定期監控 USER_MVIEWS.STALENESS 狀態，及時發現同步異常