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 ....
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
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
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 |