INVALID Materialized View with COMPILATION_ERROR

In this example, Andrew grants access on a table to a role then grants this role to John. John creates a materialized view on Andrew’s table. Andrew updates his table, making John’s materialized view INVALID. John then tries to compile his materialized view, which gives it a COMPILE_STATE of COMPILATION_ERROR. Andrew then grants access to his table directly to John and the problem goes away. It was tested on Oracle 11.2:

 

First, user Andrew creates a table with one row of data: 

SQL> conn andrew/reid

Connected.

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

 

SQL>

Then he creates a role and grants access on the table to it:

 

SQL> create role role1

  2  /

 

Role created.

 

SQL> grant select on tab1 to role1

  2  /

 

Grant succeeded.

 

SQL> 

A new user called John is set up. He has access to Andrew’s table via ROLE1 and he creates an ON DEMAND materialized view on it:

 

SQL> conn / as sysdba

Connected.

SQL> create user john identified by smith

  2  default tablespace users

  3  quota unlimited on users

  4  /

 

User created.

 

SQL> grant create session,

  2        create table,

  3        create materialized view,

  4        role1

  5        to john

  6  /

 

Grant succeeded.

 

SQL> conn john/smith

Connected.

SQL> create materialized view mv1

  2  refresh on demand

  3  as select * from andrew.tab1

  4  /

 

Materialized view created.

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         1

 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

------------------- -------

TABLE               VALID

MATERIALIZED VIEW   VALID

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

MV1                            VALID

 

SQL>

 

Andrew updates his table:

SQL> conn andrew/reid

Connected.

SQL> update tab1 set col1 = 2

  2  /

 

1 row updated.

 

SQL>

This makes John’s materialized view INVALID and gives it a COMPILE_STATE of NEEDS_COMPILE. I showed you this in an earlier example:

 

SQL> conn john/smith

Connected.

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

------------------- -------

TABLE               VALID

MATERIALIZED VIEW   INVALID

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

MV1                            NEEDS_COMPILE

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         1

 

SQL>

Johns spots this and tries to compile his materialized view but this time, it remains INVALID:

SQL> alter materialized view mv1 compile

  2  /

 

Materialized view altered.

 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

------------------- -------

TABLE               VALID

MATERIALIZED VIEW   INVALID

 

SQL>

… and it now has a COMPILE_STATE of COMPILATION_ERROR:

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

MV1                            COMPILATION_ERROR

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         1

 

SQL>

Even refreshing the materialized view does not make the problem go away:

 

SQL> exec dbms_mview.refresh('MV1');

 

PL/SQL procedure successfully completed.

 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

------------------- -------

TABLE               VALID

MATERIALIZED VIEW   INVALID

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

MV1                            COMPILATION_ERROR

 

SQL>

Although it does bring the data up to date:

SQL> select * from mv1

  2  /

 

      COL1

----------

         2

 

SQL>

Andrew grants SELECT access directly to John:

 

SQL> conn andrew/reid

Connected.

SQL> grant select on tab1 to john

  2  /

 

Grant succeeded.

 

SQL>

… and when John tries to compile the materialized view again, the problem disappears:

 

SQL> conn john/smith

Connected.

SQL> alter materialized view mv1 compile

  2  /

 

Materialized view altered.

 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

------------------- -------

TABLE               VALID

MATERIALIZED VIEW   VALID

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

MV1                            VALID

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         2

 

SQL>