oracle DBMS_Snapshot.refresh 不适用于 11g，物化视图错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6044601/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DBMS_Snapshot.refresh not working on 11g , materialized view error
提问by Nandish A
I executed DBMS_Snapshot.refreshon Oracle 10git worked fine, but when i execute the same on Oracle 11git gives the following error
我DBMS_Snapshot.refresh在Oracle 10g它上面执行得很好，但是当我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
Any help much appreciated
非常感谢任何帮助
Regards.
问候。
Nandish
南迪什
采纳答案by Nandish A
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.
您还没有在 11g 中重新创建 MV。您无法刷新 MV 来创建它。
您没有为 MV 所在的架构重新创建同义词（公共或私有）。
您没有在另一个模式中重新创建对 MV 的授权，因此请创建它们。
You might try DBMS_MVIEW instead of DBMS_SNAPSHOT.
您可以尝试 DBMS_MVIEW 而不是 DBMS_SNAPSHOT。
exec dbms_mview.refresh('Table1');
回答by Dinesh Rajan
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
我的情况有点不同。DBMS_MVIEW.REFRESH('ABC.XYZ') 调用发生在 oracle 存储过程 (SCHEMA1.PROC1) 中。我以不同的用户 (UserA) 的身份调用 SP。我已将所有权限授予用户，但没有任何反应。显然，oracle 似乎并不关心用户拥有的权限，它会查找包所有者的权限。
didnt work:
没有工作：
GRANT ALTER ANY MATERIALIZED VIEW TO UserA;
授予用户 A 任何物化视图的权限；
GRANT SELECT ON ABC.MLOG$_XYZ TO UserA;
在 ABC.MLOG$_XYZ 上向 UserA 授予选择权；
Worked:
工作过：
GRANT ALTER ANY MATERIALIZED VIEW TO SCHEMA1;
授予对 SCHEMA1 的任何物化视图的授权；
GRANT SELECT ON ABC.MLOG$_XYZ TO UserA, SCHEMA1;
将 ABC.MLOG$_XYZ 上的选择授予 UserA，SCHEMA1；
回答by Datajam
The error message suggests that it is looking for "Table1"in a case sensitive manner. Try passing in 'TABLE1'instead.
该错误消息表明它正在以"Table1"区分大小写的方式进行查找。尝试传入'TABLE1'。
