Home » SQL & PL/SQL » SQL & PL/SQL » exec dbms_mview.refresh('mviewname','c','atomic_refresh=>FALSE) is giving error (Oracle 10gR2)
exec dbms_mview.refresh('mviewname','c','atomic_refresh=>FALSE) is giving error [message #434582] Wed, 09 December 2009 16:20 Go to next message
bhanunadendla@yahoo.co.in
Messages: 3
Registered: December 2009
Junior Member
Hi All,

I am trying to refresh the materialized view using
exec dbms_mview.refresh(MVIEWNAME','c',atomic_refresh=>FALSE)

and getting the following error message:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1

First I thought there is as issue with the MVIEW script as soon I drop and recreate the mview then the above refreshmview commands works fine for this view and it is giving the same error for another mview.

I have investigated a lot but so far I don't have any soultion why it Oracle is giving this error message.

I am using Oracle 10gR2

Thanks in Advance
Bhanu
Re: exec dbms_mview.refresh('mviewname','c','atomic_refresh=>FALSE) is giving error [message #434605 is a reply to message #434582] Wed, 09 December 2009 22:46 Go to previous messageGo to next message
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
Junior Member
Hello!

Could you please enable SQL TRACE for your session (ALTER SESSION SET SQL_TRACE = TRUE;) and inspect the output to get the name of the table which it is saying about?

Re: exec dbms_mview.refresh('mviewname','c','atomic_refresh=>FALSE) is giving error [message #434634 is a reply to message #434582] Thu, 10 December 2009 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it is giving the same error for another mview.

Are you sure it is a mview?
Are you sure the underlying table exists?
Post your version with 4 decimals.

Regards
Michel
Re: exec dbms_mview.refresh('mviewname','c','atomic_refresh=>FALSE) is giving error [message #434747 is a reply to message #434582] Thu, 10 December 2009 08:48 Go to previous messageGo to next message
bhanunadendla@yahoo.co.in
Messages: 3
Registered: December 2009
Junior Member
Hi Sajith,

I have tried to enable the SQL trace but it says Insufficient privelliges.

I am pretty sure that it is not the table missing issue as I able to run the Select part of the query without any issues.

If I drop and recreate the Materialized view then the dbms_mview.refresh job will work fine and when move to next view then again the same error.

The work aorund is droping and recreating them all the time but I have like around 50 Materialized views per environment and I have aound 20 environments.

Thanks
Bhanu

Re: exec dbms_mview.refresh('mviewname','c','atomic_refresh=>FALSE) is giving error [message #434749 is a reply to message #434582] Thu, 10 December 2009 08:51 Go to previous message
bhanunadendla@yahoo.co.in
Messages: 3
Registered: December 2009
Junior Member
Hi Michel Cadot,

[Michel]Are you sure it is a mview?
[Bhanu]Yes I am sure it is a Materialized views

[Michel]Are you sure the underlying table exists?
[Bhanu]Yes I am sure the underlying table exists. I tried running the select clause of the query and it ran fine.

[Michel]Post your version with 4 decimals.
[Bhanu]you mean Oracle version? it is as follows.
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 10 09:25:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Previous Topic: "Create table from another Table" issue
Next Topic: help with 54 page pl/sql package
Goto Forum:
  


Current Time: Sat Oct 01 05:56:26 CDT 2016

Total time taken to generate the page: 0.10282 seconds