ORA-00439 When Trying To Create Materialized View With Enable Query Rewrite

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