Oracle10g Privileges to Create Materialized Views

Thursday, June 26, 2008

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