ORA-01031: Insufficient Privileges When Create a Mview in Different Schema (Doc ID 749112.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

Creating materialized view in other schema reports error ORA-01031.

User has CREATE ANY TABLE,CREATE ANY VIEW,CREATE ANY MATERIALIZED VIEW
privileges.

Testcase:-

conn /as sysdba
create user test identified by test;
grant SELECT ANY TABLE,CREATE ANY MATERIALIZED VIEW,CREATE ANY TABLE,CREATE SESSION to test;
create user user1 identified by user1;
grant resource,create session to user1;
grant unlimited tablespace to test;
conn user1/user1
create table t (a number);
alter table t add primary key (a);

conn test/test

SQL> create materialized view user1.t_mv as select * from user1.t;
create materialized view user1.t_mv as select * from user1.t
*
ERROR at line 1:
ORA-01031: insufficient privileges

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms