My Oracle Support Banner

Materialized View Configured with Subquery Factoring And Connect By Clause Remains In Compilation_Error Status (Doc ID 1357390.1)

Last updated on FEBRUARY 20, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

MVIEW defined using subquery factoring and connect by.

Example:

create materialized view <MVIEW_NAME> as
with <WITH_CLAUSE_NAME> as
(
select ...
    from ...
  where ...
start with ..
)
connect by prior ....



After DML is performed on one of the tables referenced by an mview, the mview's compile_state becomes NEEDS_COMPILE.  This state is expected (i.e. normal behavior).

Update a row in base table and check status of the mview.

update <MASTER_TABLE>
set COL1=201
where COL2=569;

1 row updated.


MVIEW_NAME                       COMPILE_STATE       STALENESS
------------------------------ ------------------- -------------------
<MVIEW_NAME>                     NEEDS_COMPILE      NEEDS_COMPILE



Compiling or refreshing the materialized view sets the compile_state to COMPILATION_ERROR.  This state is not expected, however.

alter materialized view <MVIEW_NAME> compile;


MVIEW_NAME                       COMPILE_STATE       STALENESS
------------------------------ ------------------- -------------------
<MVIEW_NAME>                   COMPILATION_ERROR   COMPILATION_ERROR


exec dbms_mview.refresh('<MVIEW_NAME>');


MVIEW_NAME                      COMPILE_STATE        STALENESS
------------------------------ ------------------- -------------------
<MVIEW_NAME>                  COMPILATION_ERROR    COMPILATION_ERROR

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.