Compile Makes Materialized View Invalid When Access to Master Table Granted Via Role

(Doc ID 781255.1)

Last updated on JULY 29, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1]
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.
SYS> connect / as sysdba
Connected.
SYS@lv102> create role ROLE1;

Role created.

SYS> grant ROLE1 to sh;

Grant succeeded.

SYS> grant select on sh.customers to scott;

Grant succeeded.

SYS> connect scott/tiger
Connected.
SCOTT> create table customers as select * from sh.customers;

Table created.

SCOTT> grant select on scott.customers to ROLE1;

Grant succeeded.

SCOTT> connect sh/sh
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@lv102> --* * * * * * * * * * * * * CREATE MV * * * * * * * * * * * * * 
SH@lv102> 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@lv102> @DBA_OBJ

OWNER      OBJECT_NAME     OBJECT_TYPE          STATUS
---------- --------------- -------------------- ---------------
SH         MV1             MATERIALIZED VIEW    VALID

SH@lv102> @MV_OBJ

OWNER      MVIEW_NAME      STALENESS            COMPILE_STATE
---------- --------------- -------------------- --------------------
SH         MV1             FRESH                VALID

SH@lv102> --* * * * * * * * * * * * * REFRESH MV * * * * * * * * * * * * * 
SH@lv102> exec dbms_mview.refresh('MV1');

PL/SQL procedure successfully completed.

SH@lv102> @DBA_OBJ

OWNER      OBJECT_NAME     OBJECT_TYPE          STATUS
---------- --------------- -------------------- ---------------
SH         MV1             MATERIALIZED VIEW    VALID

SH@lv102> @MV_OBJ

OWNER      MVIEW_NAME      STALENESS            COMPILE_STATE
---------- --------------- -------------------- --------------------
SH         MV1             FRESH                VALID

SH@lv102> --* * * * * * * * * * * * * COMPILE MV * * * * * * * * * * * * * 
SH@lv102> alter materialized view MV1 compile;

Materialized view altered.

SH@lv102> @DBA_OBJ

OWNER      OBJECT_NAME     OBJECT_TYPE          STATUS
---------- --------------- -------------------- ---------------
SH         MV1             MATERIALIZED VIEW    INVALID

SH@lv102> @MV_OBJ

OWNER      MVIEW_NAME      STALENESS            COMPILE_STATE
---------- --------------- -------------------- --------------------
SH         MV1             COMPILATION_ERROR    COMPILATION_ERROR

SH@lv102> --* * * * * * * * * * * * * REFRESH MV * * * * * * * * * * * * * 
SH@lv102> exec dbms_mview.refresh('MV1');

PL/SQL procedure successfully completed.

SH@lv102> @DBA_OBJ

OWNER      OBJECT_NAME     OBJECT_TYPE          STATUS
---------- --------------- -------------------- ---------------
SH         MV1             MATERIALIZED VIEW    INVALID

SH@lv102> @MV_OBJ

OWNER      MVIEW_NAME      STALENESS            COMPILE_STATE
---------- --------------- -------------------- --------------------
SH         MV1             COMPILATION_ERROR    COMPILATION_ERROR

 

Changes

 

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