I am running Oracle 12 in windows 7. I want to create materialized view which has ENABLE QUERY REWRITE option.
CREATE MATERIALIZED VIEW MV1
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT StoreState, TimeYear,
SUM(SalesDollar) AS SUMDollar1
FROM SSSales, SSStore, SSTimeDim
WHERE SSSales.StoreId = SSStore.StoreId
AND SSSales.TimeNo = SSTimeDim.TimeNo
AND TimeYear > 2010
GROUP BY StoreState, TimeYear;
When i tried to create and run the above statement for materialized view, i am getting error as
ORA-00439: feature not enabled: Materialized view rewrite
00439. 00000 - "feature not enabled: %s"
*Cause: The specified feature is not enabled.
*Action: Do not attempt to use this feature.
I have set the option for query_rewrite_enabled to true and query_rewrite_integrity to enforced using below statement but still i am getting the same error while creting materialized view.
alter session set query_rewrite_enabled = true ;
alter session set QUERY_REWRITE_INTEGRITY = ENFORCED;