Oracle 物化視圖建立與增量刷新完整實戰

應用場景

需從遠端 Oracle 10g 資料庫（用戶 username，SID CPEES）同步表 GG_ZLX_ZHU 至本地進行月度統計分析。遠端僅授予 SELECT ANY TABLE，無物件權限，無法使用 ODI 反向工程，故採用物化視圖 + FAST REFRESH 方案。

前置條件

• 雙端皆為 Oracle 10g，網路互通。
• 本地已在 tnsnames.ora 設定 CPEES 服務名。
• 遠端表 GG_ZLX_ZHU 擁有主鍵（FAST REFRESH 必備）。

步驟 1：建立 Database Link

CREATE DATABASE LINK to_cpees
  CONNECT TO "username" IDENTIFIED BY "passWord"
  USING 'CPEES';

步驟 2：遠端建立物化視圖日誌

必須在遠端資料庫執行，記錄主鍵變更與新舊值，支援增量刷新：

CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU
  WITH PRIMARY KEY
  INCLUDING NEW VALUES;

步驟 3：本地建立物化視圖

CREATE MATERIALIZED VIEW GG_ZLX_ZHU
  BUILD IMMEDIATE
  REFRESH FAST WITH PRIMARY KEY
  ON DEMAND
  ENABLE QUERY REWRITE
AS
  SELECT * FROM GG_ZLX_ZHU@to_cpees;

關鍵字說明：

• BUILD IMMEDIATE：立即填充資料。
• REFRESH FAST WITH PRIMARY KEY：依主鍵增量刷新。
• ON DEMAND：手動觸發刷新。
• ENABLE QUERY REWRITE：允許查詢重寫優化。

步驟 4：刷新機制自動化

使用 DBMS_MVIEW.REFRESH 封裝存儲過程，支援單一或批次刷新：

-- 單一視圖增量刷新
CREATE OR REPLACE PROCEDURE p_mview_refresh AS
BEGIN
  DBMS_MVIEW.REFRESH('GG_ZLX_ZHU', 'F');
END;
/

-- 多視圖一次刷新（需對應同樣數量的刷新模式）
CREATE OR REPLACE PROCEDURE p_mview_refresh_all AS
BEGIN
  DBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU', 'FF');
END;
/

第二參數模式：F=FAST（增量）、C=COMPLETE（完全）、?=FORCE（嘗試 FAST 失敗則 COMPLETE）。

注意事項與維護

1. 若刪除遠端 MV LOG，必須 DROP MATERIALIZED VIEW 後重建，否則無法 FAST REFRESH。
2. 遠端表必須有主鍵，否則無法 WITH PRIMARY KEY。
3. 刷新排程可結合 DBMS_SCHEDULER 或作業系統 cron 觸發存儲過程。

清理腳本

-- 遠端刪除 MV LOG
DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@to_cpees;

-- 本地刪除物化視圖
DROP MATERIALIZED VIEW GG_ZLX_ZHU;

結語

物化視圖提供「近即時、低維護」的跨資料庫同步能力。核心在於：遠端建 LOG、本地建 MV、FAST REFRESH、主鍵必備。配合排程自動化，即可滿足月度甚至日級同步需求。