ORA-01031: Insufficient Privileges while creating mview in different schema.

I will discuss one of the reason, why creation of materialized view in another schema can cause ORA-01031 error.

SQL> conn test/test

SQL> create materialized view test1.mview1
as select * from test1.table1;

ORA-01031: insufficient privileges

sqlplus / as sysdba

SQL> grant create table to test;

SQL> conn test1/test1

SQL> create materialized view test1.mview1
as select * from test1.table1;

Materialized view created.

In my case test user did not have create table permission. This permission is necessary because during creation materialized view , additional table is created automatically.

SQL> select * from dba_objects where owner=’TEST’;

OBJECT_NAME OWNER OBJECT_TYPE
———– ——- ————-
mv_table1 TEST TABLE
mv_table1 TEST MATERIALIZED VIEW

 

 

 

Advertisements