?
create
materialized
view
wxq_mv
tablespace
wxq_tbs
build
deferred
?
--
延遲刷新不立即刷新
,immediate
為立即刷新
refresh
force
??
--
如果可以快速刷新則進行快速刷新，否則完全刷新
on
demand
??????
--
按照指定方式刷新，遠程復制不能使用
commit
start
with
to_date(
'10-09-2008 16:45:10'
,
'dd-mm-yyyy hh24:mi:ss'
)
next
sysdate
+
1
with
primary
key
as
select
t.*,
sysdate
mydate
from
t8@temp_link t;
?
注1：這個過程遇到一個錯誤，在next子句后面加了注釋后編譯報錯，暈，搞了N久才發現。
注2：發現在MV自動自制JOB后到了時間沒有觸發，查了下資料，是JOB_QUEUE_INTERVA參數設置為0，alter system set job_queue_processes=10后可以自動運行。
注3：手動運行job——execute dbms_job.run(21)
?
?
4、檢查數據：
?
執行job刷新視圖后查詢數據：
?
SQL> execute dbms_job.run(26);
?
PL/SQL procedure successfully completed
?
SQL> column i format a5
SQL> column a format a5
SQL> column n format a15
SQL> column mydate format a20
?
SQL> select * from wxq_mv;
?
??? I???? A N?????????????? MYDATE
----- ----- --------------- --------------------
??? 1??? 10 aaaaaaaaaaa???? 2008-9-10 17:41:38
??? 2??? 20 bbbbbbbbbbb???? 2008-9-10 17:41:38
??? 3??? 30 ccccccccccc???? 2008-9-10 17:41:38
??? 4??? 40 ddddddddddd???? 2008-9-10 17:41:38
??? 5??? 50 eeeeeeeeeee???? 2008-9-10 17:41:38
??? 6??? 60 fffffffffff???? 2008-9-10 17:41:38
??? 7??? 70 ggggggggggg???? 2008-9-10 17:41:38
?
7 rows selected
?
在A實例中修改數據：
?
SQL> update t8 set a=100 where i=1;
?
1 row updated
?
SQL> commit;
?
Commit complete
?
再從B實例中查詢數據：
?
SQL> execute dbms_job.run(26);
?
PL/SQL procedure successfully completed
?
SQL> select * from wxq_mv;
?
??? I???? A N?????????????? MYDATE
----- ----- --------------- --------------------
??? 1?? 100 aaaaaaaaaaa???? 2008-9-10 17:47:20
??? 2??? 20 bbbbbbbbbbb???? 2008-9-10 17:47:20
??? 3??? 30 ccccccccccc???? 2008-9-10 17:47:20
??? 4??? 40 ddddddddddd???? 2008-9-10 17:47:20
??? 5??? 50 eeeeeeeeeee???? 2008-9-10 17:47:20
??? 6??? 60 fffffffffff???? 2008-9-10 17:47:20
??? 7??? 70 ggggggggggg???? 2008-9-10 17:47:20
?
7 rows selected
?
從時間可以看到，是全部刷新的
?
?
5、創建log后檢查數據：
?
在A實例中創建materialized view log
?
SQL> create materialized view log on t8 with rowid;
Materialized view log created
?
在B實例中建立的物化視圖：
?
SQL> create materialized view wxq_mv2
? 2? tablespace wxq_tbs
? 3? build deferred? --延遲刷新不立即刷新,immediate為立即刷新
? 4? refresh fast??? --使用增量刷新
? 5? on demand?????? --按照指定方式刷新
? 6? start with to_date('10-09-2008 16:45:10', 'dd-mm-yyyy hh24:mi:ss')
? 7? next sysdate + 1
? 8? with rowid
? 9? as
?10? select * from t8@temp_link?t;
?
Materialized view created
?
注：這里不能加入sysdate字段了，因為遠程快速刷新只能是簡單的表復制
??? 只有創建materialized view log 后才可以refresh fast
?
?
6、查看已經創建的materialized view：
?
SQL> select query from dba_mviews where mview_name='WXQ_MV2';
?
QUERY
--------------------------------------------------------------------------------
SELECT "T"."I" "I","T"."A" "A","T"."N" "N" FROM
"T8"@TEMP_LINK.SINATAY.COM
"T"
?
發現已經把*自動轉化為每個列名，這跟view是一樣的
所以，當遠程數據庫A增加列時，對本地B的mv不產生影響，B將忽略新增的列
但是當A改變原有的列名，或刪除了B的mv引用的列名時，mv更新時就會報錯
?
?
7、物化視圖將新建一個表、一個job：
?
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='WXQ_MV2';
?
OWNER????????????????????????? TABLE_NAME???????????????????? TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
WANGXIAOQI???????????????????? WXQ_MV2??????????????????????? WXQ_TBS
?
SQL>?desc WXQ_MV2
Name Type????????? Nullable Default Comments
---- ------------- -------- ------- --------
I??? INTEGER????????????????????????????????
A??? NUMBER??????? Y????????????????????????
N??? VARCHAR2(100) Y???
?
SQL> select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;
?
JOB?? LOG_USER????????LAST_DATE????LAST_SEC????NEXT_DATE?? NEXT_SEC??INTERVAL???? WHAT
----- --------------- ------------ ----------- ----------- --------- ------------ ----------------------------------------------
29??? WANGXIAOQI??????2008-9-17? ? 09:48:10????2008-9-17?? 14:04:12??sysdate + 1? dbms_refresh.refresh('"WANGXIAOQI"."WXQ_MV2"');
?
每個mv都會對應一個實體表和一個job
?
?
8、查看是否會創建主鍵：
?
當原始表T8沒有主鍵時，MV只能創建為with rowid模式
當然生成的mv table也是沒有主鍵的
?
當原始表T8含有主鍵時，必須要重新生成log，然后再創建mv時必須使用with primary key
?
SQL> alter table t8 add constraint t8_key primary key(i);
?
Table altered
?
SQL> drop materialized view log on t8 ;
?
Materialized view log dropped
?
SQL> create materialized view log on t8 ;
?
Materialized view log created
SQL> select constraint_name,table_name,status from user_constraints where table_name='T8';
?
CONSTRAINT_NAME??????????????? TABLE_NAME???????????????????? STATUS
------------------------------ ------------------------------ --------
T8_KEY???????????????????????? T8???????????????????????????? ENABLED
?
然后在B創建MV：
?
SQL> create materialized view wxq_mv2
? 2? tablespace wxq_tbs
? 3? build deferred? --延遲刷新不立即刷新,immediate為立即刷新
? 4? refresh fast??? --使用增量刷新
? 5? on demand?????? --按照指定方式刷新
? 6? start with to_date('10-09-2008 16:45:10', 'dd-mm-yyyy hh24:mi:ss')
? 7? next sysdate + 1
? 8? with?primary key?? --使用rowid時報錯
? 9? as
?10? select * from t8@temp_link?t;
?
Materialized view created
?
SQL> select constraint_name,table_name,status from dba_constraints where table_name='WXQ_MV3';
?
CONSTRAINT_NAME??????????????? TABLE_NAME???????????????????? STATUS
------------------------------ ------------------------------ --------
T8_KEY???????????????????????? WXQ_MV3??????????????????????? ENABLED
?
生成的表中已經含有主鍵和索引。
?
?
?
9、關于使用on prebuilt table在已有表上建立MV的操作和應用：
?
先創建一個新表T2：
?
SQL> create table t2 (a int primary key,b varchar2(10));
?
Table created