OraFAQ Forum: SQL & PL/SQL » Stale Materialised view

Home » SQL & PL/SQL » SQL & PL/SQL » Stale Materialised view (Oracle 10.2.0.4.0)

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

Stale Materialised view [message #650601]

Thu, 28 April 2016 04:45

brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom

Member

I have a materialized view set to refresh on commit and it's not refreshing when the base tables change. Looking at dba_mviews the 'staleness' column is 'IMPORT'. Does anyone know what this means and how it gets into this state?

I finally resolved the problem by manually refreshing the mview and it refreshed (last refresh date changed) and the 'staleness' column changed to 'FRESH'. But I need to know what caused it.

thanks.

Report message to a moderator

Re: Stale Materialised view [message #650609 is a reply to message #650601]

Thu, 28 April 2016 05:46

martijn
Messages: 286
Registered: December 2006
Location: Netherlands

Senior Member

Are you quite sure about the staleness column being "import"?
When I look in the documentation for your version (10.2.0.4) I see the following:
Quote:

FRESH - Materialized view is a read-consistent view of the current state of its masters

STALE - Materialized view is out of date because one or more of its masters has changed. If the materialized view was FRESH before it became STALE, then it is a read-consistent view of a former state of its masters.

UNUSABLE - Materialized view is not a read-consistent view of its masters from any point in time

UNKNOWN - Oracle Database does not know whether the materialized view is in a read-consistent view of its masters from any point in time (this is the case for materialized views created on prebuilt tables)

UNDEFINED - Materialized view has remote masters. The concept of staleness is not defined for such materialized views.

Report message to a moderator