萬盛學電腦網

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

Oracle 物化視圖跨庫同步實戰:從建立日誌到定時刷新全流程

架構拓撲

  • 源庫 (統計庫):192.168.1.81,用戶 userb 擁有表 test1 (含 PK)。
  • 目標庫 (測試庫):192.168.1.96,用戶 ts12_st 持有物化視圖 test1_st2。

源庫端配置

1. 建立只讀查詢用戶


CONN / AS SYSDBA
CREATE USER snap_query IDENTIFIED BY snap_query
  DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CONNECT TO snap_query;
GRANT SELECT ON userb.test1 TO snap_query;

2. 建立物化視圖日誌 (MView Log)


CONN userb/pwd
CREATE MATERIALIZED VIEW LOG ON userb.test1
  TABLESPACE ts12
  WITH PRIMARY KEY
  EXCLUDING NEW VALUES;
-- 必須有 PRIMARY KEY,支援 FAST REFRESH

目標庫端配置

1. 設定 JOB_QUEUE_PROCESSES (自動刷新必須)


SHOW PARAMETER spfile; -- 確認使用 SPFILE
ALTER SYSTEM SET job_queue_processes = 10 SCOPE=SPFILE;
-- 需重啟實例生效

2. 建立表空間與用戶


CREATE TABLESPACE ts_event 
  DATAFILE 'I:\ts_event_01.dbf' SIZE 5000M,
           'I:\ts_event_02.dbf' SIZE 5000M;

CREATE USER ts12_st IDENTIFIED BY ts12_st_2010
  DEFAULT TABLESPACE ts_event TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, CREATE SNAPSHOT TO ts12_st;
GRANT CREATE DATABASE LINK TO ts12_st;

3. 配置 TNS 別名並建立 DB Link


-- tnsnames.ora 新增
TS12TJ = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.81)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod)))

CONN ts12_st/ts12_st_2010
CREATE DATABASE LINK dblink_snap_query
  CONNECT TO snap_query IDENTIFIED BY snap_query
  USING 'ts12tj';

-- 測試連通性
SELECT event_id, event_type FROM userb.test1@dblink_snap_query WHERE rownum < 10;

4. 建立物化視圖 (支援快速刷新)


CREATE MATERIALIZED VIEW test1_st2
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE
  NEXT SYSDATE + 12/24  -- 每 12 小時
AS
SELECT event_id, event_code, call_time, tele_x, tele_y, event_type,
       event_content, road_code, place, direction, place_x, place_y,
       update_time, main_assistant_road
FROM userb.test1@dblink_snap_query;
  • REFRESH FORCE:優先 FAST REFRESH (需 MView Log),不可用則 COMPLETE REFRESH。
  • NEXT SYSDATE + 12/24:每 12 小時自動刷新 (依賴 JOB_QUEUE_PROCESSES)。

源庫後續授權 (關鍵步驟)


CONN / AS SYSDBA @源庫
GRANT SELECT ON mlog$_test1 TO snap_query;
-- MView Log 內部表 mlog$_ 必須授權給查詢用戶,否則 FAST REFRESH 失敗

手工刷新與監控


-- 手工刷新
EXEC DBMS_MVIEW.REFRESH('TEST1_ST2');

-- 監控刷新狀態
SELECT mview_name, last_refresh_type, last_refresh_date, 
       staleness, compile_state
FROM user_mviews;

長期維護建議

  • MView Log 空間回收:長期運行後 mlog$_test1 膨脹,執行 ALTER TABLE mlog$_test1 MOVE TABLESPACE ts12; 回收空間並重建索引。
  • 刷新效能:300 萬行約 1 小時,可考慮分區表 + Partition Change Tracking (PCT) 加速。
  • 網路中斷恢復:FORCE 模式自動降級為 COMPLETE REFRESH,確保最終一致性。
  • 版本升級:12c+ 可用 REFRESH FAST ON COMMIT 實現近實時同步。
copyright © 萬盛學電腦網 all rights reserved