Materialized View Configured with Subquery Factoring And Connect By Clause Remains In Compilation_Error Status

(Doc ID 1357390.1)

Last updated on JUNE 17, 2013

Applies to:

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

Symptoms

MVIEW defined using subquery factoring and connect by.

Example:

create materialized view sc_portfolios as
with pfl 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 is NEEDS_COMPILE.  This state is expected (i.e. normal behavior).

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

update org_nodes
set process_id=201
where rollup_key=569 and node_key=583;

1 row updated.


MVIEW_NAME                       COMPILE_STATE       STALENESS
------------------------------ ------------------- -------------------
SC_PORTFOLIOS                     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 SC_PORTFOLIOS compile;


MVIEW_NAME                       COMPILE_STATE       STALENESS
------------------------------ ------------------- -------------------
SC_PORTFOLIOS                    COMPILATION_ERROR   COMPILATION_ERROR


exec dbms_mview.refresh('sc_portfolios');


MVIEW_NAME                      COMPILE_STATE        STALENESS
------------------------------ ------------------- -------------------
SC_PORTFOLIOS                   COMPILATION_ERROR    COMPILATION_ERROR

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