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

---

---

用户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.