Tuesday, March 19, 2013

Invalid Materialized View due to Dml On Master Table


Materialized views can become invalid after a dml executed on the base table.(For example a local materialized view, its master tables is on the same database.. In this situation, dba_objects.status will show invalid, and also dba_mviews.compile_state will show Needs Compile.. (except refresh on commit materialized views -- because they are refreshed on commit..)

This invalid state will not prevent the MV to be queried, but the MV should be refreshed in order to make it return the latest data. And also, on this refresh the status of the MV will be valid again..

execute dbms_mview.refresh('MV_NAME');




3 comments :

  1. I tried to refresh the materialized view, however, it is still invalid.
    The view can be queried though.
    So, what we did was to recreate the materialized view and it became valid again.

    - Adhika

    ReplyDelete
  2. Hi Adhika,

    Did you executed execute dbms_mview.refresh(YOUR_'MV_NAME') ?
    And also what was the default refresh option for the problematic MV (I mean refresh option written in creation script of your MV)?

    ReplyDelete
  3. Try to create MV log to overcome the invalid state.

    SQL> create materialized view log on emp with rowid;

    Materialized view log created.

    SQL>
    SQL> create materialized view emp_mv
    2 refresh fast on demand
    3 with rowid
    4 as select * from emp;

    Materialized view created.

    ReplyDelete