OraFAQ Forum: SQL & PL/SQL » Materialized View Refresh Issue

Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View Refresh Issue (Oracle 11g 64 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized View Refresh Issue [message #644274]

Mon, 02 November 2015 08:14

fatimacognos
Messages: 1
Registered: November 2015
Location: Bangalore

Junior Member

Hi All,
We have batch script to refresh the materialized view automatically on 1st of every month.

We got the error below during the MV refresh yesterday (1st of Nov, 2015).

ERROR at line 1:
ORA-12057: materialized view "XXX_DB"."XXX_MV" is INVALID and must
complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at "XXX_DB.LOAD_MV_MONTHLY", line 251
ORA-06512: at line 1

Version: Oracle 11g
Recently our Oracle data warehouse has been migrated into Super Cluster pltform.

Could anyone please help on this urgently.

Thanks in advance

Report message to a moderator

Re: Materialized View Refresh Issue [message #644277 is a reply to message #644274]

Mon, 02 November 2015 08:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Could anyone please help on this urgently.

please explain why it is urgent for me to solve this problem for you.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Materialized View Refresh Issue [message #644279 is a reply to message #644274]

Mon, 02 November 2015 08:45

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ORA-12057: materialized view or zonemap \"%s\".\"%s\" is invalid and must use complete refresh
 *Cause:  The status of the materialized view or zonemap was invalid and an
          attempt was made to fast refresh it.
 *Action: Perform a complete refresh of the materialized view or zonemap.

Report message to a moderator

Re: Materialized View Refresh Issue [message #644387 is a reply to message #644274]

Wed, 04 November 2015 04:46

JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar

Member

The error is very clear: you can't do a fast refresh, before you have done a complete refresh:

dbms_mview.refresh(list => '&&mv',method => 'c',atomic_refresh => false);
--> ATOMIC_REFRESH = FALSE; a TRUNCATE instead of a DELETE at complete refresh.

If you want to speed up the refresh, disable any indexes on the MV and rebuild them afterwards.

Report message to a moderator

Previous Topic:	Update blob Column
Next Topic:	Out of SQL Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 15:00:54 CDT 2024