Oracle12c 创建物化视图,用union就会报错,报错内容:ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性
不使用union,给每个单独的sql创建没问题,只要有union就会报错。我用的是增量刷新,commit提交方式。
创建视图的sql内容如下:
create materialized view log on table_A with rowid ;
create materialized view log on table_B with rowid;
create materialized view log on table_C with rowid ;
create materialized view log on table_D with rowid ;
create materialized view VM_SN_APPLY_PARSE_INFO
REFRESH FORCE ON COMMIT
AS
select a.rowid as aid,
b.rowid as bid,
c.rowid as cid,
d.rowid as did,
.......
from table_A a,
table_B b,
table_C c,
table_D d
where a.is_effective = 1
......
union
select a.rowid as aid,
b.rowid as bid,
c.rowid as cid,
d.rowid as did,
.......
from table_A a,
table_B b,
table_C c,
table_D d
from table_A a,
table_B b,
table_C c,
table_D d
where spd.is_effective = 2
......