Create Materialized Views Privileges

Oracle10g Privileges to Create Materialized Views

grant query rewrite to scott;

grant create materialized view to scott;

alter session set query_rewrite_enabled = true;

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.
REDDYS> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
________________________________________
REDDYS CREATE MATERIALIZED VIEW NO

Roles granted are listed below for completeness of the topic.
REDDYS> select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
________________________________________
REDDYS CONNECT NO YES NO
REDDYS 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.
REDDYS> create table t1 as select owner,object_name,object_type from all_objects;
Table created.
REDDYS> 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.
REDDYS> 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.
REDDYS> 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.