物化视图快速刷新报 ORA-12008 & ORA-01031

最新推荐文章于 2023-09-27 16:13:22 发布

转载最新推荐文章于 2023-09-27 16:13:22 发布 · 654 阅读

物化视图快速刷新报 ORA-12008 & ORA-01031

用户A创建了物化视图，物化视图里边引用了用户B的表，现在用户C执行exec dbms_mview.refresh ('MV_pi_to_res_lhr' , 'F' );报错：
ORA-12008: error in materialized view refresh path
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

解决：
GRANT CREATE MATERIALIZED VIEW TO USER_NAME;
grant GLOBAL QUERY REWRITE, ON COMMIT REFRESH, ALTER ANY MATERIALIZED VIEW to USER_NAME;
grant flashback on 基表 to USER_NAME; --需要将基表的所有权限赋权给创建物化视图的用户
grant flashback on 物化视图日志表 to USER_NAME;

ORA-12008 & ORA-01031 When Trying to do a Fast Refresh on an MView (文档 ID 810117.1)

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 10.2.0.3 [Release 10.2]
Information in this document applies to any platform.

SYMPTOMS

The following error is raised when trying to perform a fast refresh of a Materialized View (MV):

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

The base table for the Materialized View contains XML or object types and is located in a different schema than the MV. If the same MV is created in the schema that owns the base table, then the refresh is working fine.

CAUSE

This issue is documented in Bug 6146742 and was confirmed for RDBMS versions 10.2.0.1 to 10.2.0.3.
It is caused by the internal use of the 'Flashback' feature of the database. Please see the Oracle documentation for more details on this feature.

The bug is currently open with Development and is being worked upon there.

SOLUTION

There is a workaround available to make the refresh run:

Granting explicitly flashback privilege on the base table and the MV log to the owner of the MV avoids the issue:
Please replace 'PARENT_TABLE' with the name of the base table and '' with the owner of the Materialized View.

grant flashback on PARENT_TABLE to ;
grant flashback on MLOG$_PARENT_TABLE to ;

REFERENCES

BUG:6146742 - ORA-12008,01031 WHEN REFRESH FAST SNAPSHOT WITH XMLTYPE OF PARENT IN DIF SCHEMA.

About Me

...............................................................................................................................

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-05-05 09:00 ~ 2017-05-05 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。