Oracle物化視圖建立執行個體

公司的項目中遇到需要從其它系統的資料庫中取資料進行統計分析的問題，初步選擇使用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要求要在住表上建立物化視圖日誌。

CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU

WITH PRIMARY KEY

INCLUDING NEW VALUES;

(上面的SQL要在遠端資料庫上執行，不能在本地執行)

3、建立物化視圖

物化視圖，從名字上面來開，它應該是屬於視圖，但是確實物化。其物化是針對普通視圖並沒有真正的實體儲存體而言，其實可以簡單的把物化視圖看做一個物理表（不再做具體解釋）。

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;