Materialized View - REFRESH ON COMMIT Error
697768Jun 20 2009 — edited Jun 20 2009I'm trying to change the refresh mode of a materialized view from ON DEMAND to ON COMMIT and I'm getting an error:
Error at Command Line:4 Column:9
Error report:
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at commit time.
*Action: Specify only valid options.
The original DDL is
--------------------
CREATE MATERIALIZED VIEW TEST_MV
REFRESH FORCE ON DEMAND
AS SELECT a.field1, MAX (DECODE (a.field2, 1, field3, NULL)) lbl
FROM (SELECT field1, field2, field3 FROM source_table GROUP BY field1, field2, field3 ) a
GROUP BY a.field1;
--------------------
and I tried
--------------------
CREATE MATERIALIZED VIEW TEST_MV
REFRESH FORCE ON COMMIT
AS SELECT a.field1, MAX (DECODE (a.field2, 1, field3, NULL)) lbl
FROM (SELECT field1, field2, field3 FROM source_table GROUP BY field1, field2, field3 ) a
GROUP BY a.field1;
--------------------
WITH ROWID option
How can I change the refresh mode for such a materialized view?
Thank you.