DBMS_MVIEW.REFRESH Fails With ORA-01031

jzzw

于 2020-04-08 12:03:20 发布

阅读量870

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/u014710633/article/details/105384598

Oracle 专栏收录该内容

56 篇文章

订阅专栏

本文介绍了解决在Oracle数据库中，当尝试刷新其他用户拥有的物化视图时遇到的ORA-01031权限不足错误的方法。通过授予ALTER ANY MATERIALIZED VIEW权限给需要刷新视图的用户，可以有效解决这一问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Problem statement:

user_a owns a materialized view (mv_demo);
user_b wants to refresh the mv_demo mview in the user_a schema;
dbms_refresh fails with ORA-01031: insufficient privileges.

Example:

SQL> exec dbms_mview.refresh('user_a.mv_demo');
BEGIN dbms_mview.refresh('user_a.mv_demo'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
ORA-06512: at line 1

Solution:

grant ALTER ANY MATERIALIZED VIEW to user_b.