ORA-01031: insufficient privileges while creating MATERIALIZED VIEW

One of the things i have found by coincidence in Oracle 11g is when you try to create a materialized view by a non-schema user (even if he has a ‘DBA‘ privilege), you will face the following error:

ERROR at line 1: ORA-01031: insufficient privileges

 

although the user is powerful but he could not create the materialized view under any schema (in the following example the schema name is “sico“).

SQL> CREATE MATERIALIZED VIEW sico.test_mv AS SELECT * FROM dual;
CREATE MATERIALIZED VIEW sico.test_mv AS SELECT * FROM dual
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

you can create a materialized view using the schema owner after granting him the “create table” privilege.

 

check it and see !!!

I personally faced this in Oracle 11gR2.