Mview Refresh Fails and COMPILE_STATE => COMPILATION_ERROR (Doc ID 1081493.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

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

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