Materialized view will become invalid after a refresh - documented Oracle bug in older versions+ workaround.

Posted by David Yahalom under Oracle

Today I stumbled across a very annoying Oracle bug regarding Materialized Views becoming invalid after a refresh. I’m talking about Oracle Bug 2639679 (QUERY_REWRITE flag changes for MVIEW with DATE RANGE in WHERE) which affects Oracle 8i and even some versions of 9i (can’t be sure which versions exactly as Metalink says this is resolved in 9i but clearly this is not the case).

When you create a materialized view with DISABLE QUERY REWRITE option, when you refresh the materialized view (using DBMS_SNAPSHOT.REFRESH, DBMS_REFRESH.REFRESH or any other method), the QUERY REWRITE flag will automatically be turned back on. This is a problem if your materialized view has a where clause in it as QUERY REWRITE + a where clause is a no-no.

This bug will cause your materialize view to become invalid every time you refresh it. Very annoying.

The solution I found was to add a disable query rewrite command before and after the refresh of the materialized view.

EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite');
DBMS_SNAPSHOT.REFRESH( 'MV_NAME','C');
EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite');

You can put this in a database job to have the materialized view refresh automatically.

Hope this helps.