Oracle10g Privileges to Create Materialized Views

In a previous post, I mentioned the privileges required to create materialized views in Oracle8i and Oracle9i Standard Edition. In Oracle10g, the requirement appears to be much more simplified.

Just needed to grant the CREATE MATERIALIZED VIEW system privilege.

MIKE@statest> select * from user_sys_privs;


USERNAME      PRIVILEGE                         ADM

MIKE CREATE MATERIALIZED VIEW NO

Roles granted are listed below for completeness of the topic.

MIKE@statest> select * from user_role_privs;


USERNAME       GRANTED_ROLE             ADM DEF OS_

MIKE CONNECT NO YES NO MIKE RESOURCE NO YES NO

We are running Oracle10g Release 2 Standard Edition in which QUERY REWRITE is not an enabled feature. Describing what I mean, I create a test table below upon which the materialized view will be based.

MIKE@statest> create table t1 as select owner,object_name,object_type from all_objects;

Table created.

MIKE@statest> select count(*) from t1;

COUNT(*)
———-
4196

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

MIKE@statest> 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.

MIKE@statest> 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.

References
Oracle Database 10g Product Family

Additional information on materialized views from Oracle8i to Oracle11g can be found on this post.

Advertisements

3 Responses to Oracle10g Privileges to Create Materialized Views

  1. […] public links >> materialized Oracle10g Privileges to Create Materialized Views Saved by mjrindewitt on Sat 04-10-2008 My first SL contact materialized… Saved by GeoffTyrer on […]

  2. comments.blog….

    […]Oracle10g Privileges to Create Materialized Views « Mike R's Blog[…]…

  3. Venkat Rao says:

    Materialized views are creating, but my requested query(optimizer) created based on the materialized view doesn’t go to the materialized view due to lack of ENABLE QUERY REWRITE.
    How to Enable QUERY REWRITE In Materialized View

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: