ORA 942 on mview refresh
- From: Robert Laverty <Robert.Laverty@xxxxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Mar 2012 08:08:48 -0700
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
