Tuesday, March 18, 2014

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>

1 comment:

Unknown said...

Hi,
Nice post , so is this Oracle bug , as i facing same issue on 12c , please suggest

Should i directly grant access to all tables instead of roles

thanks