Materialized View Configured with Subquery Factoring And Connect By Clause Remains In Compilation_Error Status
(Doc ID 1357390.1)
Last updated on AUGUST 04, 2018
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 ....
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
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
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
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 |
| Cause |
| Solution |
| References |