Change Materialized View Refresh Mode Makes Materialized View Unusable

The materialized view was in good status until when changes its refresh mode:

SQL>  select MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD,STALENESS 
      dba_mviews 
      where mview_name='MVIEW1';

MVIEW_NAME     REFRES     REFRESH_  STALENESS  
-------------- ---------- --------- ----------
MVIEW1         COMMIT     FAST      FRESH    


SQL> alter materialized view MVIEW1 refresh fast on demand;



SQL>  select MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD,STALENESS 
      dba_mviews 
      where mview_name='MVIEW1';

MVIEW_NAME     REFRES     REFRESH_  STALENESS  
-------------- ---------- --------- ------------------
MVIEW1         COMMIT     FAST      COMPILATION_ERROR 

 

SQL> alter materialized view MVIEW1 compile;

Materialized view altered.


SQL>  select MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD,STALENESS 
      dba_mviews 
      where mview_name='MVIEW1';

MVIEW_NAME     REFRES     REFRESH_  STALENESS  
-------------- ---------- --------- --------------------
MVIEW1         COMMIT     FAST      UNUSABLE

It might be a bug ? not quite sure why. In this situation, the materialized view has to be refreshed completed or rebuilt.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: