创建物化视图是默认是不可更新的，要创建可以更新的物化视图必须加上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);
1 row created.
yang@rac1>insert into t values(3,'lily',3);
1 row created.
yang@rac1>insert into t values(4,'yang',4);
1 row created.
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>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>create table t as select object_id id ,object_name name from user_objects;
Table created.
yang@rac1>create materialized view log on t with rowid;
Materialized view log created.
yang@rac1>create materialized view mv_t refresh fast with rowid for update as
2 select * from t;
Materialized view created.
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 row created.
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
1 yang
23 rows selected.
查看物化日志，没有关于更新的记录。
yang@rac1>select * from mlog$_t;
no rows selected