在oracle 10g的数据库中，将一个物化视图做了一个简单的条件子句修改，删除后重建，但是，就建不成功，报ora-00942表或视图不存在错误。
这个问题很奇怪，我首先想这会是一个bug吗？找了oracle的metalink，还真有类似bug记载，，但给出的解决方法不合适。于是去自己分析解决，然后发现一个坑接着一个坑啊。
好吧，我先介绍物化视图的创建语句，如下所示：
create materialized view v_tablename
refresh complete on demand
start with to_date('28-05-2013 16:55:32', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1
as
select wid as wid, kcm as kcm, jxbh as jxbh, kknf as kknf, kkxqm as kkxqm, xq as xq, js as js, zs as zs, jsgh as jsgh
from usr_gxsj.v_tablename@dblink_name
where
(kknf = '2012' and kkxqm = '1') or (kknf = '2012' and kkxqm = '2');
ora-00942: table or view does not exist
ora-06512: at sys.dbms_snapshot_utl, line 960
ora-06512: at line 1
在oracle的metalink中，一个名为create materialized view results in : ora-942 [id 364632.1] 文档是这样解释说它是一个bug。
symptoms
creating a materialized view based on a view existing on the remote database results in the following errors:
ora-00942: table or view does not exist
ora-06512: at sys.dbms_snapshot_utl, line 1543
ora-02063: preceding 2 lines from remote db
significantly, the database link on the local side connects to user_c schema on the remote database.
on the remote database the configuration is :
user_a - table owner and materialized view log owner;
user_b - has view on a table in user_a's schema : view1
user_c - has select privs on view in user_b's schema.
changes
this issue occurs when the remote database is 10.2.
the problem did not occur with 9.2.x
cause
this issue is addressed in : bug 5015547.
solution
in order to determine that it is this issue, create the database link to user_b schema.
this can serve as a workaround and confirmation that this is likely : bug 5015547
apply patch for bug 5015547 to 10.2.0.x if it is thought you are experiencing this issue.
在这篇文档中，oracle提出的解决方法，是将dblink修改成同一访问用户的，这里没去测试，因为这不符合我们的应用架构的规划。
没办法了，只好去自己去分析并在网上找找前人的案例。
首先，检查一下权限，看看显示授权行不行。将usr_gxsj.v_tablename的授权,不行，将usr_gxsj.v_tablename中的表再授权，也不行。
后来，在网上发现有一个人给出了这个问题的解决方法，将usr_gxsj.v_tablename@dblink_name这个数据源用select * from usr_gxsj.v_tablename@dblink_name做一次嵌套。
按照方法测试了一下，居然就可以了。
create materialized view v_tablename
refresh complete on demand
start with to_date('28-05-2013 16:55:32', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1
as
select wid as wid, kcm as kcm, jxbh as jxbh, kknf as kknf, kkxqm as kkxqm, xq as xq, js as js, zs as zs, jsgh as jsgh
from (select * from usr_gxsj.v_tablename@dblink_name)
where
(kknf = '2012' and kkxqm = '1') or (kknf = '2012' and kkxqm = '2');
这里很悲剧，因为我们没搞懂为什么？？？
后来，发现这还不是最悲剧的，我们在添加工作记录时，顺手翻了以前的记录。
去年的记录
修改视图v_tablename，报错
ora 00942 table or view does not exist
ora 06512 at sys.dbmc_snapshot_utl ,line 960
给了dba的权限，还是报一样的错误，想办法。。。。
。。。。。
开始说是bug，打了补丁后还是不对。
使用10046 event分析语句内部执行时遇到的具体错误
是再校验基础表的主键字段出错。
我们物化视图脚步中，没有指明是使用rowid还是primary key方式遍历数据。默认使用primary key。
根据记录提示，我们增加with rowid子句，创建成功。
原来，这个问题以前发生过，我们在一个坑里摔了两次，希望没第三次了。