为什么我把基表和物化视图建在同一个库上可以给物化视图设置自动快速刷新功能,但是如果基表在另一个库上,物化视图通过dblink连接基表,就不能设置自动快速刷新功能,创建的时候报错:
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
数据库是10.2.0.1,脚本在下面,请大侠帮忙看看啊,谢谢哈!
成功脚本是:
PROD1@PROD>create table t1 (x int,y int,z int);
Table created.
PROD1@PROD>insert into t1 values (1,2,3);
1 row created.
PROD1@PROD>insert into t1 values (4,5,6);
1 row created.
PROD1@PROD>commit;
Commit complete.
PROD1@PROD>create materialized view log on t1 with sequence,rowid (x,y,z) including new values;
Materialized view log created.
PROD1@PROD>create materialized view mv_t1 build immediate refresh with rowid fast on commit enable query rewrite as select x,y from t1;
Materialized view created.
失败脚本是:
PROD2@PROD2>create table t1 (x int,y int,z int);
PROD2@PROD2>insert into t1 values (1,2,3);
PROD2@PROD2>insert into t1 values (4,5,6);
PROD2@PROD2>commit;
PROD2@PROD2>select * from t1;
PROD2@PROD2>create materialized view log on t1 with sequence,rowid (x,y,z) including new values;
Materialized view log created.
PROD1@PROD>drop materialized view mv_t1;
Materialized view dropped.
PROD1@PROD>create materialized view mv_t1 build immediate refresh with rowid fast on commit enable query rewrite as select x,y from t1@dblink_prod2;
create materialized view mv_t1 build immediate refresh with rowid fast on commit enable query rewrite as select x,y from t1@dblink_prod2
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
网上有人说“on commit的一个前提就是物化视图和基表必须处于一个数据库中”。不知道这个说法对不对。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26521853/viewspace-1097819/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26521853/viewspace-1097819/