RE: ORA 942 on mview refresh
- From: "Stephens, Chris" <Chris.Stephens@xxxxxxx>
- To: Robert Laverty <Robert.Laverty@xxxxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- 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
PARSE
#47255742560304:c=0,e=136,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1332963261014991
...
...
...
BINDS #47255742560304:
Bind#0
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=1332963261027969
XCTEND 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@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!
-----Original Message-----
From: Robert Laverty [mailto:Robert.Laverty@xxxxxxxxxxxxxxxxxxxx]
Sent: Thursday, March 29, 2012 10:09 AM
To: oracle-l@xxxxxxxxxxxxx
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.
Bob Laverty
------------------------------
From: "Stephens, Chris" <Chris.Stephens@xxxxxxx>
Date: Tue, 27 Mar 2012 15:22:36 -0500
Subject: ORA 942 on mview refresh
11.2.0.3 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?
Chris
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.
CONFIDENTIALITY NOTICE:
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
