How to solve ORA-01031: insufficient privileges when creating a materialized view

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)