My Oracle Support Banner

Creating Materialized View in a Different Schema Fails with ORA-01031 (Doc ID 263650.1)

Last updated on MARCH 10, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

Symptoms



- While trying to create a materialized view in another schema, you encounter the ORA-01031 error.

Eg :

1. Let us consider two users u1 and u2.

2. The user u2 has been granted SELECT ANY TABLE, CREATE TABLE privileges

3. The user u2 has also been granted the following system privileges via a role U2_ROLE

SQL> select PRIVILEGE from role_sys_privs where role='U2_ROLE';
PRIVILEGE
----------------------------------------
CREATE SNAPSHOT
ON COMMIT REFRESH
GLOBAL QUERY REWRITE

SQL> select GRANTED_ROLE from DBA_ROLE_PRIVS where GRANTEE='U2';
GRANTED_ROLE
------------------------------
CONNECT
U2_ROLE
RESOURCE


4. Now connect as user u1 and issue :

create materialized view u2.test_mv
build immediate
refresh fast
on commit
enable query rewrite
as
select * from u1.test;


This fails with ORA-01031: insufficient privileges

5. When the 'ON COMMIT' and 'QUERY REWRITE' clauses are removed, the statement works fine.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.