Oracle物化视图创建报ORA-00942错的解决
来源:互联网 发布:程序员写文档的工具 编辑:程序博客网 时间:2024/06/07 07:56
在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中,一个名为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子句,创建成功。
原来,这个问题以前发生过,我们在一个坑里摔了两次,希望没第三次了。。。。
这个问题很奇怪,我首先想这会是一个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子句,创建成功。
原来,这个问题以前发生过,我们在一个坑里摔了两次,希望没第三次了。。。。
- Oracle物化视图创建报ORA-00942错的解决
- 创建物化视图时报错ora-00600
- 创建Oracle物化视图
- Oracle创建物化视图
- Oracle物化视图创建
- oracle 物化视图导入导出报错
- Oracle创建dblink报错:ORA-01017、ORA-02063解决
- Oracle创建dblink报错:ORA-01017、ORA-02063解决
- Oracle创建dblink报错:ORA-01017、ORA-02063解决
- Oracle创建dblink报错:ORA-01017、ORA-02063解决
- Oracle创建dblink报错:ORA-01017、ORA-02063解决
- 创建物化视图错误ORA-12052的解决方法
- ORA-12052,不能建立快速刷新物化视图的解决
- 物化视图的创建
- oracle创建视图报ora-01031
- oracle的物化视图
- oracle的物化视图
- Oracle的物化视图
- 队列
- 赵雅智:android介绍及开发环境搭建
- hdoj_2009 求数列的和
- Linux 内核相关的统计数据
- 建议的程序员学习LDA算法的步骤
- Oracle物化视图创建报ORA-00942错的解决
- 浅谈PHP第三弹---使用二分查找法查找数组中的元素位置
- 修改MariaDB(Mysql)数据库密码
- UML总结概述
- 开源项目
- 定时任务:Java中Timer和TimerTask的使用
- 有关Android线程的学习
- vim的使用
- 【eoeAndroid社区索引】android数据存储之SQLite教程实例汇总