00942 oracle_Oracle物化视图创建报ORA-00942错误解决

在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 [email protected]_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