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 18.104.22.168 and later
Information in this document applies to any platform.
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.
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.
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