Posted on 2011-08-11 15:26
瘋狂 閱讀(48212)
評論(5) 編輯 收藏 所屬分類:
database
我們如果遇到需要從其它系統的數據庫中取數據進行統計分析的問題，可疑選擇使用ORACLE的ODI工具進行抽數，但是對方提供的數據庫用戶下沒有任何對象，只是有查詢所有表的權限，因此無法做數據反向。
于是決定使用物化視圖，把對方數據庫中的數據拿過來，雖然數據量比較大，但是每月只拿一次，而且如果設置成增量更新，也不會太慢。現在記錄下物化視圖的創建過程（以一張表為例）。
一、準備條件以及備注
假設雙方數據庫都是ORACLE10g，需要同步過來的表名叫：GG_ZLX_ZHU，對方數據庫用戶名：username，密碼：password，SID：CPEES。
二、開始干活
1、首先要創建DB_LINK
- CREATE DATABASE LINK to_cpees
-
- CONNECT TO "username" identified by "password"
-
- using "CPEES"
其中CPEES為新建的到對方數據庫的TNS。執行，現在我們就已經創建了到對方數據庫的DB_LINK TO_CPEES。
2、創建Oracle物化視圖快速刷新日志
因為上面說過，以后視圖的刷新將采用增量刷新的方式，因此，為配合增量刷新，ORACLE要求要在住表上建立物化視圖日志。
- CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU
-
- WITH PRIMARY KEY
-
- INCLUDING NEW VALUES;
-
(上面的SQL要在遠程數據庫上執行，不能在本地執行)
3、創建Oracle物化視圖
Oracle物化視圖，從名字上面來開，它應該是屬于視圖，但是確實物化。其物化是針對普通視圖并沒有真正的物理存儲而言，其實可以簡單的把物化視圖看做一個物理表（不再做具體解釋）。
- CREATE MATERIALIZED VIEW GG_ZLX_ZHU --創建物化視圖
-
- BUILD IMMEDIATE --在視圖編寫好后創建
-
- REFRESH FAST WITH PRIMARY KEY --根據主表主鍵增量刷新（FAST，增量）
-
- ON DEMAND -- 在用戶需要時，由用戶刷新
-
- ENABLE QUERY REWRITE --可讀寫
-
- AS
-
- SELECT * FROM GG_ZLX_ZHU@TO_CPEES; --查詢語句
4、視圖刷新
根據業務需要，每月不定時刷新，所以不能是JOB，而且數量多，所以也不能一個一個刷新。根據以上條件，選擇使用ORACLE自帶工具DBMS_MVIEW工具包中REFRESH方法對物化視圖進行刷新。該方法有兩個參數，第一個參數是需要刷新的物化視圖名稱，第二個參數是刷新方式。我們可以寫存儲過來，對每個物化視圖調用一次REFRESH方法，也可以使用“，”把物化視圖連接以來，一次刷新。如下：
- CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS
-
- BEGIN
-
- DBMS_MVIEW.REFRESH('GG_ZLX_ZHU','f');
-
- END P_MVIEW_REFRESH;
-
或者使用
- CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS
-
- BEGIN
-
- DBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU','ff');
-
- END P_MVIEW_REFRESH;
-
注意:
1、如果需要同時刷新多個物化視圖，必須用逗號把各個物化視圖名稱連接起來，并對
每個視圖都要指明刷新方式（f、增量刷新，c、完全刷新，?、強制刷新）。
2、當日志和物化視圖創建好后，刪除日志，則需要重新創建物化視圖，否則無法增量
刷新。
3、因為上面寫的物化視圖時根據主鍵進行更新，因此，主表必須有主鍵。
4、以上文章中紅色是為可替換的，大家可以根據自己項目需求來修改。
希望上面的內容對大家能有幫助。
忘了寫刪除方法了，日志和物化視圖要分開刪除
- DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@TOCPEES;
-
- DROP MATERIALIZED VIEW GG_ZLX_ZHU;