ORA-00439 When Trying To Create Materialized
View With Enable Query Rewrite
1. When trying to create a materialized view
in SQL*Plus an error is returned:
CREATE MATERIALIZED VIEW store_sales_mv
PCTFREE 0 TABLESPACE mviews
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
PARALLEL
BUILD
DEFERRED
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT
s.store_name,
SUM(dollar_sales) AS sum_dollar_sales
FROM
store s, fact
WHERE f.store_key = s.store_key
GROUP BY s.store_name;
ORA-00439: feature not enabled: %s
Cause: The specified feature is not enabled.
Action: Do not attempt to use this feature.
3. The ENABLE QUERY REWRITE option is removed
from the above statement and
the error goes away and the statement processes successfully.
Solution Description:
The proper options must be installed to use
some features of the materialized views. The query 'select * from
V$option' will show what options are installed. The options
needed to use enable query rewrite and refresh capabilities
are:
Parameter Value
Materialized view rewrite TRUE -> For ENABLE QUERY REWRITE
Materialized view warehouse refresh TRUE -> For REFRESH
In addition, the following initialization
parameters must also be set:
QUERY_REWRITE_ENABLED = default is set to
FALSE, can change to TRUE
QUERY_REWRITE_INTEGRITY = ENFORCED,
STALE_TOLERATED, TRUSTED
Source : Oracle Metalink
