关于物化视图 on commit刷新的问题请教

[rzlink]

create materialized view VIEW_A_B
refresh fast on commit as
(select a.id as a_id
b.name as b_name
from a left join b
on a.id = b.id);


这样建视图 说不能用on commit 刷新
怎么样才能建on commit的物化视图
a b 都建了view log 了
a表是分区表 跟这个有关系吗

[tangren]

无日志，无真相。

[rzlink]

ora-12054: cannot set the on commit refresh attribute for the materialized view
出的是这个错

[suiziguo]

贴出详细错误信息。

[TTOS3302041]

权限不足

[ojuju10]

我也遇到过类似问题，好像与版本也有关系

[robin_ares]

In addition for M.V.'s with Single-Table Aggregates and Materialized Views
with Joins and Aggregates, there are some more conditions on refresh
to the ones mentioned above:
Joins and Aggregates :
=====================

i)The WHERE clause can contain inner equi-joins only
(that is, no outer joins)
ii)Materialized views from this category are FAST refreshable after
Direct Load to the base tables; they are not FAST refreshable after
conventional DML to the base tables.
iii)Materialized views from this category can have only the
ON DEMAND option (so, the on-commit cannot be used for this category

j就是説多表不可以on-commit
想想也是，让视图监视所有表的事务，代价太大

[zhyl0219]

在创建的时候也遇到类似问题，我这操作的时候提示错误：ORA-01031 权限不足

[半世码农]

学习中!

[gg_lihui]

也遇到相同的问题，不知道LZ是怎么解决的？

[minitoy]

这样?

SQL> create materialized view VIEW_A_B
  2   refresh force   on commit as
  3  select a.empno,a.ename,b.dname
  4     from emp a ,dept b
  5   where b.deptno=a.deptno(+)
  6  /
 
Materialized view created
 
SQL>