After DML on the Master Table(s) of Local Materialized View, USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE' and USER_OBJECTS.STATUS becomes 'INVALID' (Doc ID 264036.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later

Information in this document applies to any platform.

SYMPTOMS

After executing a DML statement on base table(s) of MV that is on the same database as its master table(s) (i.e. a local MV), USER_OBJECTS.STATUS becomes INVALID for the MV.  Also, USER_MVIEWS.COMPILE_STATE shows status "NEEDS_COMPILE'.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      VALID

SQL> update emp set empno = empno where empno = 605;

SQL> commit;

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                      COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                       NEEDS_COMPILE

CHANGES

DML was executed and committed on master table of the MV since the MV was last refreshed.

CAUSE

This is expected behavior. Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

SOLUTION

Though the status is INVALID, the MV can still be queried.  However, the query on MV will not return the latest data in master table unless the MV is refreshed.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      NEEDS_COMPILE

SQL> select count(*) from emp_mv_pk;

Output :

COUNT(*)

----------

2686976

- On next refresh, the status becomes VALID.

Example :

SQL> execute dbms_mview.refresh('emp_mv_pk');

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      VALID

"REFRESH ON COMMIT" MVs are the exception to this behavior because they are refreshed at the same time of DML.