0

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.

I tried 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
           table.

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.

1

Updatable materialized view was an Advanced Replication feature. A writable materialized view was simply an updatable materialized view that was not a member of a materialized view (replication) group.

Desupport of Advanced Replication

Starting in Oracle Database 12c release 2 (12.2), the Advanced Replication feature of Oracle Database is desupported.

The Oracle Database Advanced Replication feature is desupported in its entirety. The desupport of this feature includes all functionality associated with this feature: multimaster replication, updateable materialized views, hierarchical materialized views, and deployment templates. Read-only materialized views are still supported with basic replication.

You are looking for a solution for a feature that became deprecated 7 years ago (in 12.1) and desupported 4 years ago (12.2).

The FOR UPDATE clause still exists for backward compatibility, but it is not even documented anymore.

I do not know why Oracle implemented it this way, but at this point, I would just let it go.

For the use case you described, a simple table is fine. You can do incremental refreshes with MERGE on key + timestamp, and do a complete refresh with truncate + insert ... select ....

2
  • I hadn't noticed that it was desupported. A sister organization has asked to use GoldenGate and was denied from the upstream partner. I will adjust my solution accordingly.
    – IMarvinTPA
    Jul 14 '20 at 3:28
  • @IMarvinTPA Yes, Oracle also suggest using GG instead of this. The problem is: GG is an extra cost option. Jul 14 '20 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.