建立物化視圖是預設是不可更新的，要建立可以更新的物化視圖必須加上for update 關鍵字。
yang@rac1>create table t (id number ,name varchar2(30),val number);
Table created.
yang@rac1>create materialized view log on t with rowid,sequence (id,name) including new values;
Materialized view log created.
yang@rac1>create materialized view mv_t_id refresh fast as select id ,count(1) from t group by id;
Materialized view created.
yang@rac1>insert into t values(1,'a',1);
1 row created.
yang@rac1>insert into t values(2,'b',2);
yang@rac1>insert into t values(3,'lily',3);
yang@rac1>insert into t values(4,'yang',4);
yang@rac1>select * from mv_t_id;
ID COUNT(1)
---------- ----------
1 1
2 1
3 1
5 1
6 2
向物化視圖中插入資料。
yang@rac1>insert into mv_t_id values (7,1);
insert into mv_t_id values (7,1)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
-删除物化日志之後再次嘗試插入，仍然報錯。
yang@rac1>drop materialized view log on t;
Materialized view log dropped.
建立可更新的物化視圖：
建立測試表并建立物化日志。
yang@rac1>create table t as select object_id id ,object_name name from user_objects;
yang@rac1>create materialized view log on t with rowid;
yang@rac1>create materialized view mv_t refresh fast with rowid for update as
2 select * from t;
yang@rac1>select * from mv_t;
ID NAME
---------- ---------------
130864 YANG_SEQ
132031 YANG_A
132032 SYS_C0066382
132033 YANG_B
132034 SYS_C0066383
132035 FACT
132036 MLOG$_YANG_A
132037 MLOG$_YANG_B
132038 MLOG$_FACT
132039 T
131949 YANG_ROWID
131951 YANG_PK
131952 SYS_C0066303
131955 YANG_OBJECT
131956 YANG_OID
131957 SYS_C0066304
132018 YANG_C
132017 MV_CAPABILITIES_TABLE
132030 MLOG$_YANG_PK
132027 MLOG$_YANG_ROWID
LINKORACL
LINKYANG
22 rows selected.
yang@rac1>insert into mv_t values (1,'yang');
1 yang
23 rows selected.
檢視物化日志，沒有關于更新的記錄。
yang@rac1>select * from mlog$_t;
no rows selected