My Oracle Support Banner

Mview Refresh Fails and COMPILE_STATE => COMPILATION_ERROR (Doc ID 1081493.1)

Last updated on AUGUST 04, 2018

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

Before and after materialized view refresh the status is INVALID and COMPILATION_ERROR.


> select owner,mview_name,refresh_mode,refresh_method,last_refresh_type, last_refresh_date,staleness,compile_state from user_mviews where mview_name= 'DAV_TIME_PERIOD';

OWNER MVIEW_NAME REFRES REFRESH_ LAST_REF LAST_REFR STALENESS COMPILE_STATE
----------------- -------------------------- -------- -------- --------- ------------------- -------------------
MARS_AUDIT DAV_TIME_PERIOD DEMAND FORCE COMPLETE 02-MAR-10 COMPILATION_ERROR COMPILATION_ERROR

> select object_name,object_type,status from user_objects where object_name='DAV_TIME_PERIOD';

OBJECT_NAME        OBJECT_TYPE               STATUS
------------                   ------------------                   -------
DAV_TIME_PERIOD TABLE                            VALID
DAV_TIME_PERIOD MATERIALIZED VIEW     INVALID

 

> exec dbms_mview.refresh('DAV_TIME_PERIOD', atomic_refresh => FALSE);
PL/SQL procedure successfully completed.



> select owner,mview_name,refresh_mode,refresh_method,last_refresh_type, last_refresh_date,staleness,compile_state from user_mviews where mview_name= 'DAV_TIME_PERIOD';

OWNER MVIEW_NAME REFRES REFRESH_ LAST_REF LAST_REFR STALENESS COMPILE_STATE
----------------- -------------------------- -------- -------- --------- ------------------- -------------------
MARS_AUDIT DAV_TIME_PERIOD DEMAND FORCE COMPLETE 02-MAR-10 COMPILATION_ERROR COMPILATION_ERROR


> select object_name,object_type,status from user_objects where object_name='DAV_TIME_PERIOD';

OBJECT_NAME OBJECT_TYPE                        STATUS
------------             ------------------                          -------
DAV_TIME_PERIOD TABLE                              VALID
DAV_TIME_PERIOD MATERIALIZED VIEW       INVALID



Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.