在Oracle10g的数据库中,将一个物化视图做了一个简单的条件子句修改,删除后重建,但是,就建不成功,报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中
这个问题很奇怪,我首先想这会是一个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中