Cause of the Problem
The ON COMMIT clause of CREATE MATERIALIZE VIEW has some restrictions. Such as,
•ON COMMIT clause is not supported for materialized views containing object types.
•With ON COMMIT clause inserting into the materialized view table(master table) is not supported by selecting rows from remote tables.
Try using "on demand" which is default.
SQL>CREATE MATERIALIZED VIEW MV_test
2 REFRESH fast on demand WITH ROWID
3 for update
4 as SELECT
7 TO_CHAR ('0') as status
8 FROM test_m
Materialized view created.
SQL> DROP MATERIALIZED VIEW MV_test;
Materialized view dropped.
Colud you please change WITH ROWID to -------> WITH PRIMARY KEY
REFRESH fast on commit WITH ROWID need to REFRESH fast on commit WITH PRIMARY KEY
Please read below link:
I don't think you can mix "on commit" with "for update". Maybe the "for update" code path simply assumes that the master table is remote (which would introduce the "on commit" restriction) and doesn't actually check.
Why do you want to be able to change the content of the MV ? Given the right circumstances you might be able to create a clone table with the same primary key and use a simple set of triggers to achieve the effect you want with a much smaller overhead anyway.