Permissions Required to Create a Materialized View

The idea for this post came from a problem, which I saw on Javier Morales Carreras' blog here.

This example was tested on Oracle 11.2. It shows the permissions required to create a materialized view. First I created a user:

 

SQL> conn / as sysdba

Connected.

SQL> create user andrew identified by reid

  2  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

I logged in as the user and tried to create a materialized view. This failed (obviously):

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

create materialized view mv1 as select * from dual

                                              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

I granted create materialized view to the user:

 

SQL> conn / as sysdba

Connected.

SQL> grant create materialized view to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

…but when I logged in as the user and tried to create a materialized view again, this failed too:

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

create materialized view mv1 as select * from dual

                                              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

This is because you also need the create table privilege before you can create a materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> grant create table to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

When I tried to create a materialized view this time, I saw a different error:

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

create materialized view mv1 as select * from dual

                                              *

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

 

SQL>

 

I attended to the ORA-01950 and the user was then able to create a materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> alter user andrew quota unlimited on users

  2  /

 

User altered.

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

 

Materialized view created.

 

SQL>

 

You need to be able to create tables because a materialized view has an underlying table itself:

 

SQL> select object_name, object_type from user_objects

  2  /

 

OBJECT_NAME     OBJECT_TYPE

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

MV1             TABLE

MV1             MATERIALIZED VIEW

 

SQL>

 

Oracle does not allow you to drop the table as this would stop the materialized view working:

 

SQL> drop table mv1

  2  /

drop table mv1

           *

ERROR at line 1:

ORA-12083: must use DROP MATERIALIZED VIEW to drop

"ANDREW"."MV1"

 

SQL>

 

Conversely, if you drop a materialized view, the associated table disappears too:

 

SQL> drop materialized view mv1

  2  /

 

Materialized view dropped.

 

SQL> select object_name, object_type from user_objects

  2  /

 

no rows selected

 

SQL>