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)

Last updated on MAY 31, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 12.2.0.1 [Release 9.0.1 to 12.2]
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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms