Solution for ORA-01031: insufficient privileges during MView job

Today I created a materialized view, but during the corresponding job running, an error occurred.
The following text is the error message in the Oracle alert file:

Mon Aug 13 19:00:54 2012
Errors in file /opt/oracle/admin/dacdb/bdump/dacdb_j000_20861.trc:
ORA-12012: error on auto execute of job 225
ORA-12008: error in materialized view refresh path
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

The materialized view was created by a DBA user, but why still “insufficient privileges”?
Finally I found the solution:
Grant a dba role to the user is USELESS!
The privileges must be EXPLICITLY granted!

eg:

GRANT SELECT ON dw_meta.md_dw_cbu_indicator_info TO CBU_DP;
GRANT SELECT ON dw_meta.md_dw_cbu_table_info TO CBU_DP;
GRANT SELECT ON dw_meta.md_dw_cbu_report TO CBU_DP;


Data Warehouse For Ever原创文章,转载请注明出处

  1. It was dark when I woke. This is a ray of sunnsihe.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>