At first, I got the following error from the Oracle server when exeuting the “CREATE MATERIALIZED VIEW” script:
where tab2.year > to_number(to_char(sysdate, 'YYYY')) - 4
*
ERROR at line 36:
ORA-01031: insufficient privileges
Solution:
If you try to create a materialized view based on tables in a different schema, you need the privilege
GLOBAL QUERY REWRITE
as well as
CREATE TABLE
CREATE MATERIALIZED VIEW
Grant the privileges:
grant GLOBAL QUERY REWRITE to scott; grant CREATE TABLE to scott; grant CREATE MATERIALIZED VIEW to scott;
Verify your users privileges:
connect scott/tiger select * from user_sys_privs; USERNAME PRIVILEGE ADM ------------------------------ ---------------------------------------- --- EKSTERN CREATE SESSION NO EKSTERN CREATE TABLE NO EKSTERN CREATE MATERIALIZED VIEW NO EKSTERN GLOBAL QUERY REWRITE NO
This should fix your ORA-01031 problems.
Source: Oracle Support “Create Local Materialized View with Query Rewrite Option Fails with ORA-1031 Insufficient Privileges” (Doc ID 1079983.6)
