Tag: UNUSABLE materialized view

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.