Re: ORA 942 on mview refresh
Date: Mon, 2 Apr 2012 11:04:41 -0700
Message-ID: <CAM4Kh5Q-sC0_YB=4uZPx26U2=2LBa9Qw3F+J8GxwuQkfvBA=UQ_at_mail.gmail.com>
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_at_adm.com>wrote:
> 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-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 02 2012 - 13:04:41 CDT