How do I ensure that an Oracle Materialized View is created as a "Writeable" materialized view and not as an "Updatable" one? I never want these views to push data back to the source and I want a complete refresh to overwrite anything I change.
The source problem is that one of the remote databases cannot support materialized view logs, but the tables have last modified timestamp columns. So I'd like to attempt to keep them up to date with Merge statements filtering on that column and then occasionally do normal replication refreshes if any issues turn up.
CREATE MATERIALIZED VIEW Test.SomeTable_MVW TABLESPACE SOME_DATA BUILD DEFERRED REFRESH COMPLETE FOR UPDATE AS (SELECT ... FROM SomeTable@OverThere);
But I got the error:
12013. 00000 - "updatable materialized view must be simple enough and have a materialized view log on its master table to do fast refresh"
*Cause: The updatable materialized view query contained a JOIN, SUBQUERY,
UNION, CONNECT BY, ORDER BY, or GROUP BY clause, or the master
table did not have a materialized view log.
*Action: Make the materialized view simpler. If a join is really needed,
make multiple simple materialized views then put a view on top of
them. Make sure a materialized view log is created for the master
There is only one remote table. I do not understand why this error even contains
or the master table did not have a materialized view log since that's how you get a writeable materialized view. Too bad there isn't a "FOR WRITEABLE" clause for when you know that you don't want to push data back. And the REFRESH COMPLETE should clue it in that I don't want to push the data back.