3

I have a materalized view I've been using for quite awhile now where I perform a fast refresh every night prior to another job. The PROC gets called through an automated system (Active Batch), and contains the following refresh: 

DBMS_MVIEW.REFRESH(LIST=>'GLMV_TAX_CODE_HIST',PARALLELISM => 8);

A few weeks ago, I randomly began getting this error:

Exit Code Description: ORA-12008: error in materialized view refresh path
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748

After a bit of research, figured it was simply a matter of granting permissions based on my findings, so ran the following, which appeared to work:

GRANT SELECT ON MLOG$_TAX_CODE_HIST TO MYUSER;
GRANT CREATE ANY materialized view to MYUSER;
GRANT ALTER ANY MATERIALIZED VIEW TO MYUSER;
Grant ALTER ANY SNAPSHOT to MYUSER;
Grant create session, alter any materialized view to MYUSER;

Figured it might have been an issue with the user I was calling the PROC from, but even after changing it to the creator of the Mview and such, still getting the same error.... Now the strange part is, if I login via client app (Toad) and I run the refresh manually as the same user, it works fine, no errors, but then as soon as I try to call it via "Active Batch" workflow automation, it happens again.... Anyone have any idea what I'm missing here?

Burhan Ali
  • 2,258
  • 1
  • 28
  • 38
denisb
  • 787
  • 5
  • 18
  • 31
  • Virtual columns? http://richardfoote.wordpress.com/2010/11/24/11g-virtual-columns-and-fast-refreshable-materialized-views-what-in-the-world/ – David Aldridge Oct 21 '13 at 12:17
  • Just ran the following from that link and it doesn't appear I have any virtual columns no. – denisb Oct 21 '13 at 13:16
  • Could it be something as simple as grants going via roles rather than directly to the user? – Colin 't Hart Oct 21 '13 at 15:15
  • 1
    Or does this https://forums.oracle.com/thread/1047732 help? – Colin 't Hart Oct 21 '13 at 15:17
  • It's kind of a mystery at this point for me unless you guys can tell me some of the "Grants" are missing from my initial post. All of the grants are set directly on the user and not via role. Even when running the refresh as the "creator" of the MVIEW which has grants on the underlying tables and view logs, I still get the permission error. What makes it even more confusing is that my refresh works when running it manually. I just have this problem when automated, but the automated version is running the same user (creator). Thanks for the responses by the way! – denisb Oct 21 '13 at 16:42
  • 1
    I just ran this and will see if it will make any difference in tonight's run (exec DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => 'myuser');) – denisb Oct 21 '13 at 16:46
  • It would appear the above solved it! Thanks!! – denisb Oct 23 '13 at 11:12

0 Answers0