- 來源端 (各分公司) 建立 MV Log:
CREATE MATERIALIZED VIEW LOG ON scott.emp WITH ROWID, SEQUENCE (empno, ename, sal, deptno) INCLUDING NEW VALUES;
- 總部建立對應 Schema、表空間、DB Link:
CREATE USER sh_oracl IDENTIFIED BY pwd DEFAULT TABLESPACE sh_ts;
GRANT CONNECT, RESOURCE, CREATE MATERIALIZED VIEW TO sh_oracl;
CREATE DATABASE LINK sh_link CONNECT TO scott IDENTIFIED BY pwd USING 'SH';
- 總部建立實體化視圖 (FAST REFRESH 每日):
CREATE MATERIALIZED VIEW sh_oracl.emp_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
START WITH SYSDATE NEXT TRUNC(SYSDATE+1) + 1/24 -- 每天 01:00
AS SELECT * FROM scott.emp@sh_link;
同理建立 hz_oracl.emp_mv@HZ_LINK、wh_oracl.emp_mv@WH_LINK。
- 排程自動刷新:
BEGIN
DBMS_REFRESH.MAKE(
name => 'DAILY_MV_REFRESH',
list => 'SH_ORACL.EMP_MV, HZ_ORACL.EMP_MV, WH_ORACL.EMP_MV',
next_date => SYSDATE, interval => 'TRUNC(SYSDATE+1)+1/24',
implicit_destroy => FALSE, rollback_seg => '', push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
- 監控與錯誤處理:
- 查詢
DBA_MVIEWS、DBA_MVIEW_LOGS、DBA_REFRESH_CHILDREN。
- FAST REFRESH 失敗 (MV Log 被清除/截斷) → 自動轉 COMPLETE 或手動
DBMS_MVIEW.REFRESH('mv_name','C')。