ORA 942 on mview refresh
Date: Thu, 29 Mar 2012 08:08:48 -0700
Message-ID: <2500EFA954A6424E9B188BFDCD37EBF101036920ED2C_at_CORPMAIL.molina.mhc>
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_at_adm.com>
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 29 2012 - 10:08:48 CDT