An unmoderated forum for the discussion of anything to do with using Oracle databases.

Robert Laverty | 29 Mar 2012 17:08

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@...>
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.

(Continue reading)

headers

Stephens, Chris | 29 Mar 2012 20:47

RE: ORA 942 on mview refresh

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 <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

(Continue reading)

headers

Stephens, Chris | 30 Mar 2012 20:27

RE: ORA 942 on mview refresh

Just to follow up on this.  Oracle support is suggesting this is due to bug # 13791213.
Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later [Release: 11.1 and later ]
Information in this document applies to any platform.
Goal

Getting error ora-942 on mview refresh intermittently.Even though the user has all the privileges needed
to refresh.

ORA-12048: error encountered while refreshing materialized view "O_PRODUCT"."WEBSITE_PSS_MV"
ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545 ORA-06512: at
"SYS.DBMS_SNAPSHOT", line 2751 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720 ORA-06512: at line 2

Solution

The issue is caused due to Bug 13791213 which is fixed in 11.2.0.4.

Bug 13791213 - ORA-942 ERROR ON MVIEW REFRESH

REDISCOVERY INFORMATION:
ORA-942 when refreshing MAV with self joins of tables with name of length >= 22

WORKAROUND:
Use table names of length < 22
Or set parameter _mav_refresh_opt to 32

The previous issue I had associated with Materialized Views that I received help on from the list was due to
bug 12845115.

(Continue reading)

headers

Mad Amruthur | 2 Apr 2012 20:04

Re: ORA 942 on mview refresh

Hi Chris,
I was the one that worked with Oracle to get this bug documented...
We were frustrated with this issue as well and finally after a lot of
research I narrowed it down to the fact when my mv logs (we had 3 of them
for this MV) had deletes and inserts in them the issue started to surface
with the fast refresh failing.

The deletes cause the MV refresh to use a MERGE statement and I started
playing with Oracle's MERGE for the MV and I started playing around with
the name of the MV and by accident ended up realizing that it had to do
with the number of characters in the MV log name...

If the base table name was less than 22 characters the refresh worked well
but if more it was failing with the 942 error...

I had to create a full test case to get Oracle development to work on it
and they documented the bug and produced a patch. I have tested the patch
and it fixes the issue.

I have tested the _mav parameter in our dev environments but might end up
just applying the patch in production when I get a chance...

Please let me know if you need more information...

Thanks
Mad

On Fri, Mar 30, 2012 at 11:27 AM, Stephens, Chris <Chris.Stephens@...>wrote:

> Just to follow up on this.  Oracle support is suggesting this is due to

(Continue reading)