Compile Makes Materialized View Invalid When Access to Master Table Granted Via Role
(Doc ID 781255.1)
Last updated on AUGUST 06, 2020
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1]Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Materialized view (MV) gets created without error. Its status is VALID in DBA_OBJECTS. 'ALTER MATERIALIZED VIEW <MV> COMPILE' makes the MV invalid, DBA_MVIEWS.COMPILE_STATE shows COMPILATION_ERROR. MV refresh does not change the status, after refresh the status is invalid if the status was invalid before refresh. Similarly status remains valid, if the status was valid before the refresh. Query in the MV's DDL runs fine as the owner of the MV. If the MV gets invalid e.g. due to DMLs on the base table, neither ALTER MATERIALIZED VIEW ... COMPILE, nor complete refresh makes it valid.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
SYS> connect / as sysdba
Connected.
SYS@xxxx> create role ROLE1;
Role created.
SYS> grant ROLE1 to sh;
Grant succeeded.
SYS> grant select on sh.customers to scott;
Grant succeeded.
SYS> connect <USER>/<PASSWORD>
Connected.
USER> create table customers as select * from sh.customers;
Table created.
USER> grant select on scott.customers to ROLE1;
Grant succeeded.
USER> connect sh/<PASSWORD>
Connected.
SH> --* * * * * * * * * * * * * QUERY BEHIND THE MV * * * * * * * * * *
SH> select count(*) from
2 (
3 SELECT c.cust_id,
4 SUM(amount_sold) AS dollar_sales
5 FROM sales s, scott.customers c
6 WHERE s.cust_id= c.cust_id
7 GROUP BY c.cust_id
8 );
COUNT(*)
----------
7059
SH@xxxx> --* * * * * * * * * * * * * CREATE MV * * * * * * * * * * * * *
SH@xxxx> create materialized view MV1
2 BUILD IMMEDIATE
3 refresh complete
4 as
5 SELECT c.cust_id,
6 SUM(amount_sold) AS dollar_sales
7 FROM sales s, scott.customers c
8 WHERE s.cust_id= c.cust_id
9 GROUP BY c.cust_id;
Materialized view created.
SH@xxxx> @DBA_OBJ
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- -------------------- ---------------
SH MV1 MATERIALIZED VIEW VALID
SH@xxxx> @MV_OBJ
OWNER MVIEW_NAME STALENESS COMPILE_STATE
---------- --------------- -------------------- --------------------
SH MV1 FRESH VALID
SH@xxxx> --* * * * * * * * * * * * * REFRESH MV * * * * * * * * * * * * *
SH@xxxx> exec dbms_mview.refresh('MV1');
PL/SQL procedure successfully completed.
SH@xxxx> @DBA_OBJ
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- -------------------- ---------------
SH MV1 MATERIALIZED VIEW VALID
SH@xxxx> @MV_OBJ
OWNER MVIEW_NAME STALENESS COMPILE_STATE
---------- --------------- -------------------- --------------------
SH MV1 FRESH VALID
SH@xxxx> --* * * * * * * * * * * * * COMPILE MV * * * * * * * * * * * * *
SH@xxxx> alter materialized view MV1 compile;
Materialized view altered.
SH@xxxx> @DBA_OBJ
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- -------------------- ---------------
SH MV1 MATERIALIZED VIEW INVALID
SH@xxxx> @MV_OBJ
OWNER MVIEW_NAME STALENESS COMPILE_STATE
---------- --------------- -------------------- --------------------
SH MV1 COMPILATION_ERROR COMPILATION_ERROR
PL/SQL procedure successfully completed.
SH@xxxx> @DBA_OBJ
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- -------------------- ---------------
SH MV1 MATERIALIZED VIEW INVALID
SH@xxxx> @MV_OBJ
OWNER MVIEW_NAME STALENESS COMPILE_STATE
---------- --------------- -------------------- --------------------
SH MV1 COMPILATION_ERROR COMPILATION_ERROR
Connected.
SYS@xxxx> create role ROLE1;
Role created.
SYS> grant ROLE1 to sh;
Grant succeeded.
SYS> grant select on sh.customers to scott;
Grant succeeded.
SYS> connect <USER>/<PASSWORD>
Connected.
USER> create table customers as select * from sh.customers;
Table created.
USER> grant select on scott.customers to ROLE1;
Grant succeeded.
USER> connect sh/<PASSWORD>
Connected.
SH> --* * * * * * * * * * * * * QUERY BEHIND THE MV * * * * * * * * * *
SH> select count(*) from
2 (
3 SELECT c.cust_id,
4 SUM(amount_sold) AS dollar_sales
5 FROM sales s, scott.customers c
6 WHERE s.cust_id= c.cust_id
7 GROUP BY c.cust_id
8 );
COUNT(*)
----------
7059
SH@xxxx> --* * * * * * * * * * * * * CREATE MV * * * * * * * * * * * * *
SH@xxxx> create materialized view MV1
2 BUILD IMMEDIATE
3 refresh complete
4 as
5 SELECT c.cust_id,
6 SUM(amount_sold) AS dollar_sales
7 FROM sales s, scott.customers c
8 WHERE s.cust_id= c.cust_id
9 GROUP BY c.cust_id;
Materialized view created.
SH@xxxx> @DBA_OBJ
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- -------------------- ---------------
SH MV1 MATERIALIZED VIEW VALID
SH@xxxx> @MV_OBJ
OWNER MVIEW_NAME STALENESS COMPILE_STATE
---------- --------------- -------------------- --------------------
SH MV1 FRESH VALID
SH@xxxx> --* * * * * * * * * * * * * REFRESH MV * * * * * * * * * * * * *
SH@xxxx> exec dbms_mview.refresh('MV1');
PL/SQL procedure successfully completed.
SH@xxxx> @DBA_OBJ
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- -------------------- ---------------
SH MV1 MATERIALIZED VIEW VALID
SH@xxxx> @MV_OBJ
OWNER MVIEW_NAME STALENESS COMPILE_STATE
---------- --------------- -------------------- --------------------
SH MV1 FRESH VALID
SH@xxxx> --* * * * * * * * * * * * * COMPILE MV * * * * * * * * * * * * *
SH@xxxx> alter materialized view MV1 compile;
Materialized view altered.
SH@xxxx> @DBA_OBJ
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- -------------------- ---------------
SH MV1 MATERIALIZED VIEW INVALID
SH@xxxx> @MV_OBJ
OWNER MVIEW_NAME STALENESS COMPILE_STATE
---------- --------------- -------------------- --------------------
SH MV1 COMPILATION_ERROR COMPILATION_ERROR
SH@xxxx> --* * * * * * * * * * * * * REFRESH MV * * * * * * * * * * * * *SH@xxxx> exec dbms_mview.refresh('MV1');
PL/SQL procedure successfully completed.
SH@xxxx> @DBA_OBJ
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- --------------- -------------------- ---------------
SH MV1 MATERIALIZED VIEW INVALID
SH@xxxx> @MV_OBJ
OWNER MVIEW_NAME STALENESS COMPILE_STATE
---------- --------------- -------------------- --------------------
SH MV1 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 |