My Oracle Support Banner

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

Last updated on SEPTEMBER 27, 2023

Applies to:

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

Symptoms


It is applicable for higher releases too 10gr1 10gr2 and 11g.

- 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


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.