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.