创建物化视图是默认是不可更新的，要创建可以更新的物化视图必须加上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