RE: ORA 942 on mview refresh
Date: Thu, 29 Mar 2012 13:47:48 -0500
Thanks for the reply but permissions are not the issue. I ran a 10046 trace and found the following:
PARSING IN CURSOR #47255742560304 len=105 dep=1 uid=0 oct=3 lid=0 tim=1332963261014992 hv=706429101 ad='162cbedc8' sqlid='c3bzqbhp1qh5d'
SELECT u.name as mowner, o.name as master FROM obj$ o, user$ u WHERE o.obj# = :1 AND o.owner# = u.user#
END OF STMT
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2afa95b77058 bln=22 avl=04 flg=05 value=599136
EXEC #47255742560304:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2024304382,tim=1332963261027905 FETCH #47255742560304:c=0,e=7,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=1,plh=2024304382,tim=1332963261027921 CLOSE #47255742560304:c=0,e=2,dep=1,type=0,tim=1332963261027969XCTEND rlbk=1, rd_only=1, tim=1332963261028063 EXEC #47255739532920:c=203969,e=335060,p=31,cr=3375,cu=10,mis=0,r=0,dep=0,og=1,plh=0,tim=1332963261028234 ERROR #47255739532920:err=942 tim=1332963261028248
Oracle is looking for an object that isn't there.
sys_at_REMGT> SELECT u.name as mowner, o.name as master FROM obj$ o, user$ u WHERE o.obj# = 599136;
no rows selected
I've got an SR open with Oracle. Now that I think about it, I feel like I've had this issue before and there was a patch for it. Someone contacted me off list to let me know about an unpublished bug. ...off to the oracle-l archives we go!
From: Robert Laverty [mailto:Robert.Laverty_at_MolinaHealthCare.Com] Sent: Thursday, March 29, 2012 10:09 AM
Cc: Stephens, Chris
Subject: ORA 942 on mview refresh
Just solved this a couple of days ago, with similar frustrations over many months due to the lack of information about the source of the error. I also worked around it for a long time by recreating the MV myself.
Finally, some of the articles and advice I found sunk in. The failures were caused by missing object permissions.
My access to the base objects used in the MV was role-based. Creating the MV was never a problem but the refresh job kept failing. Once I asked my DBA for direct select permissions on the base objects, problem was solved. The PL/SQL refresh package cannot work on objects without direct grants to the owner of the refresh job.
From: "Stephens, Chris" <Chris.Stephens_at_adm.com>
Date: Tue, 27 Mar 2012 15:22:36 -0500
Subject: ORA 942 on mview refresh
220.127.116.11 EE on RHEL5
I'm having a helluva time tracking down the source of an ORA-00942 error refreshing a materialized view. I've done "alter system set events '942 trace name errorstack level 3';" but that just shows the call to dbms_mview.refresh as the SQL in the trace file.
As the owner of the materialized view I can run the SQL the MV is based on + create the same MV with only a different name.
This is one of those materialized views built on a nested rabbit hole of other views and materialized views which complicates things.
In the past, I gave up trying to figure out what the issue actually was and just recreated the MV. That fixes the issue for long periods of time (months) but I'm feeling pretty bad about having to tell those affected that I haven't been able to figure it out.
Does anyone have any further advice on how to figure out that the failing table or view is?
IMPORTANT NOTICE TO RECIPIENT: This email is meant only for the intended recipient of the transmission. In addition, this email may be a communication that is privileged by law. If you received this email in error, any review, use, disclosure, distribution, or copying of this email is strictly prohibited. Please notify us immediately of the error by return email, and please delete this email from your system. Thank you for your cooperation.
This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.