What is a Materialized View
A materialized view is a replica of a target master from a single point in time. The concept was first introduced with Oracle7 termed as SNAPSHOT. In Oracle release 7.1.6 snapshots were enhanced to enable DMLs along with a new terminology, updatable snapshots. In Oracle 7.3 the primary key snapshots were commenced. Subquery snapshots, support of LOB datatypes and offline instantiation are some of the new features released with Oracle8. With Oracle 8.1.6 snapshots started to be used in data warehouse environments so a new terminology materialized view was introduced to address both distributed and data warehouse materialized views. The most remarkable MVIEW enhancements in Oracle9 are the multitier materialized views and support for user-defined types.
The Problem
Following error were obeserved from the Oracle server when the “CREATE MATERIALIZED VIEW” script was executed:
where tab2.year > to_number(to_char(sysdate, 'YYYY')) - 4
*
ERROR at line 36:
ORA-01031: insufficient privileges
The 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
You can grant the privilege as shown below:
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.