Hidden life of a Materialized View

in order to create a materialized view that can be refreshed (instead of "completely rebuilt") you must create a "materialized view log":

create materialized view log on wli_qs_report_attribute;

after that you can create the matview:

CREATE MATERIALIZED VIEW WLI_QS_REPORT_MAT_VIEW as select * from wli_qs_report_attribute;


this will generate a table mlog$_wli_qs_report_attrib listing all the changes since the last refresh:

select count(*) from mlog$_wli_qs_report_attrib;
3776

desc mlog$_wli_qs_report_attrib
Name Null Type
--------------- ---- -------------
MSG_GUID VARCHAR2(64)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255 BYTE)


After you fast refresh the WLI_QS_REPORT_MAT_VIEW MATERIALIZED VIEW, the size of mlog$_wli_qs_report_attrib will drop to 0, and the ERROR sign next to the WLI_QS_REPORT_MAT_VIEW goes away.