DBMS_Snapshot.refresh 在 11g 上不起作用，物化视图错误
我在 Oracle 10g 上执行了 DBMS_Snapshot.refresh 它工作得很好，但是当我在 Oracle 11g 上执行相同的操作时，它给出了以下错误
DBMS_SNAPSHOT.refresh('Table1','F');
BEGIN DBMS_SNAPSHOT.refresh('Table1','F'); END;
.
*
ERROR at line 1:
ORA-23401: materialized view "localuser"."Table1" does not
exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
任何帮助赞赏的
问候。
南迪什
I executed DBMS_Snapshot.refresh on Oracle 10g it worked fine, but when i execute the same on Oracle 11g it gives the following error
DBMS_SNAPSHOT.refresh('Table1','F');
BEGIN DBMS_SNAPSHOT.refresh('Table1','F'); END;
.
*
ERROR at line 1:
ORA-23401: materialized view "localuser"."Table1" does not
exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
Any help much appreciated
Regards.
Nandish
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
好吧，这可能有很多原因。
您尚未在 11g 中重新创建 MV。您无法刷新 MV 来创建它。
您没有重新创建 MV 所在架构的同义词（公共或私有）。
您没有在另一个架构中重新创建对 MV 的授权，因此请创建它们。
您可以尝试使用 DBMS_MVIEW 而不是 DBMS_SNAPSHOT。
OK, there may be many reasons for this.
You didn't recreate the MV yet in 11g. You can't refresh an MV to create it.
You didn't recreate a synonym (public or private) to the schema where the MV is located.
You didn't recreate grants to the MV in another schema, so create them.
You might try DBMS_MVIEW instead of DBMS_SNAPSHOT.
该错误消息表明它正在以区分大小写的方式查找
“Table1”。尝试传入'TABLE1'。The error message suggests that it is looking for
"Table1"in a case sensitive manner. Try passing in'TABLE1'instead.我的情况略有不同。 DBMS_MVIEW.REFRESH('ABC.XYZ') 调用来自 Oracle 存储过程 (SCHEMA1.PROC1)。我以不同用户 (UserA) 的身份调用 SP。我已经向用户授予了所有权限，但没有成功。显然，oracle 似乎并不关心用户拥有的权限，它会寻找包所有者的权限.. 这很奇怪，与我在各处准备好的相反.. 但它确实有效但
不起作用：
授予更改任何物化视图至用户A；
将 ABC.MLOG$_XYZ 上的选择授予用户 A；
工作：
授予对 SCHEMA1 任何物化视图的更改；
将 ABC.MLOG$_XYZ 上的选择授予用户 A、SCHEMA1；
My case was little different. The DBMS_MVIEW.REFRESH('ABC.XYZ') call happens from a oracle stored procedure (SCHEMA1.PROC1). I was calling the SP as a different user (UserA). I have given all permissions to the user, but to no awail. Apparently, oracle dont seem to care about the permission the user has, it looks for the permissions for the package owner.. this is very odd and contrary to what I ready everywhere.. but it did work
didnt work:
GRANT ALTER ANY MATERIALIZED VIEW TO UserA;
GRANT SELECT ON ABC.MLOG$_XYZ TO UserA;
Worked:
GRANT ALTER ANY MATERIALIZED VIEW TO SCHEMA1;
GRANT SELECT ON ABC.MLOG$_XYZ TO UserA, SCHEMA1;