Invalid Materialized View (Doc ID 1072040.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.

Symptoms

Some materialized views have status invalid, however, they compile with no errors.  The mview itself is a straight forward mview. So, this status seems inconsistent.  It appears the mview goes INVALID when we convert to a FAST REFRESH Mechanism.

Changes

The Materialized view was created as:

SQL> CREATE MATERIALIZED VIEW "RDLVR"."EMP1"  REFRESH FORCE
 ON DEMAND AS
 SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME",
 "EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE"
 "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM",
 "EMP"."DEPTNO" "DEPTNO"
 FROM "RDLVR"."EMP"@BACKPAS "EMP";


When this is viewed in the DBA_OBJECTS view, it appears as VALID:

SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS from dba_objects a where a.owner='RDLVR' and a.object_type='MATERIALIZED VIEW' and a.object_name in ('EMP1');

OWNER OBJECT_NAME OBJECT_TYPE        CREATED   STATUS
------------------------------------------------------
RDLVR EMP1         MATERIALIZED VIEW 29-01-2010 VALID


When this is viewed in the DBA_MVIEWS view, it appears as VALID:

SQL> select OWNER,MVIEW_NAME, MASTER_LINK, REFRESH_MODE, LAST_REFRESH_DATE, COMPILE_STATE from dba_mviews a where a.owner='RDLVR' and a.mview_name in ('EMP1');

OWNER MVIEW_NAME MASTER_LINK REFRESH_MODE LAST_REFRESH_DATE COMPILE_STATE
------------------------------ --------------------------------------------
RDLVR EMP1          @BACKPAS DEMAND        29-01-2010 18:09:    VALID


Alter the mview to be fast refreshable:

SQL> ALTER MATERIALIZED VIEW "RDLVR"."EMP1" REFRESH FAST;

Check the DBA_MVIEWS and DBA_OBJECTS views again:

SQL> select OWNER,MVIEW_NAME, MASTER_LINK, REFRESH_MODE, LAST_REFRESH_DATE, COMPILE_STATE from dba_mviews a where a.owner='RDLVR' and a.mview_name in ('EMP1');

OWNER MVIEW_NAME MASTER_LINK REFRESH_MODE LAST_REFRESH_DATE COMPILE_STATE
------------------------------ ------------------------------------------- -
RDLVR EMP1 @BACKPAS DEMAND 29-01-2010 18:09: COMPILATION_ERROR

SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS from dba_objects a where a.owner='RDLVR' and a.object_type='MATERIALIZED VIEW' and a.object_name in ('EMP1');

OWNER OBJECT_NAME OBJECT_TYPE CREATED STATUS
-----------------------------------------------------
RDLVR EMP1 MATERIALIZED VIEW 29-01-2010 INVALID

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