ORA 942 on mview refresh

From: Stephens, Chris <Chris.Stephens_at_adm.com>
Date: Tue, 27 Mar 2012 15:22:36 -0500
Message-ID: <D95BD5AFADBB0F4E9BB6C53F14D3A05005DCB70124_at_JRCEXC1V1.research.na.admworld.com> 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?


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.

Received on Tue Mar 27 2012 - 15:22:36 CDT

Original text of this message