RE: ORA 942 on mview refresh
Date: Fri, 30 Mar 2012 13:27:20 -0500
Message-ID: <D95BD5AFADBB0F4E9BB6C53F14D3A05005DDEB1A8C_at_JRCEXC1V1.research.na.admworld.com>
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.
Both had to do with Materialized Views but were distinct problems.
Chris
From: Stephens, Chris
Sent: Tuesday, March 27, 2012 3:23 PM
To: 'oracle-l_at_freelists.org'
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
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 30 2012 - 13:27:20 CDT