Privileges to Create Materialized Views

It is rare in our environment that a developer will request privileges to create a materialized view. So when a developer requests the required privileges to create a materialized view, I have to look up the answer. Metalink Note 1079983.6 provides some assistance.

Below are the privileges to allow the developer to create a materialized view in Oracle9i. In my case I had to add the CREATE SNAPSHOT system privilege to the already existing privileges assigned.

EASY_DEV2@stah303> select * from user_sys_privs;


USERNAME             PRIVILEGE                      ADM

EASY_DEV2 CREATE TABLE NO EASY_DEV2 CREATE ANY VIEW NO EASY_DEV2 CREATE SNAPSHOT NO

In addition, the developer is attempting to create the materialized view with query rewrite. The problem is we are running Oracle9i Release 2 Standard Edition in which QUERY REWRITE is not an enabled feature.

To describe what I mean, I create a test table below upon which the materialized view will be based.

ORACLE@stad116> create table t1 as select owner,object_name,object_type from dba_objects;

Table created.

ORACLE@stad116> select count(*) from t1;

COUNT(*)
———-
5834

However when I attempt to create the materialized view with the “enable query rewrite” clause, I encounter the “feature not enabled” error.

ORACLE@stad116> create materialized view mv_test
2 refresh complete on commit
3 enable query rewrite
4 as
5 select object_type, count(*)
6 from t1
7 group by object_type;

from t1
*
ERROR at line 6:
ORA-00439: feature not enabled: Materialized view rewrite

I remove the “enable query rewrite” clause and the materialized view is created successfully.

ORACLE@stad116> create materialized view mv_test
2 refresh complete on commit
3 as
4 select object_type, count(*)
5 from t1
6 group by object_type;

Materialized view created.

Metalink Note 1019100.102 provides an explanation for this. Although the note refers to Oracle8i, it appears to be true in Oracle9i.

References
Oracle9i Database Release 2 Product Family

Advertisements

One Response to Privileges to Create Materialized Views

  1. […] Privileges to Create Materialized Views In a previous post, I mentioned the privileges required to create materialized views in Oracle8i and Oracle9i Standard […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: