Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Materialized View - REFRESH ON COMMIT Error

697768Jun 20 2009 — edited Jun 20 2009
I'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.
This post has been answered by Aman.... on Jun 20 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 18 2009
Added on Jun 20 2009
6 comments
2,003 views