Category Archives: Materialized Views

Hacking MV Logs for Change Data Capture

MV For Replication

Consider a solution that uses Materialized View Logs in a way that was not intended by Oracle. The idea is to implement fast refresh MV capability for Oracle source and non-Oracle target. I have tested this approach to confirm that it works but I am concerned about long-term consequences of this unintended and unsupported usage.

MY_TAB is the Oracle (11.2) table that I want to mirror in another non-Oracle RDBMS.

The refresh of the target table is applied by an external process invoked by Oracle procedure in the source database. That process accepts the data set extracted from the MV Log, and applies changes to the target. Upon successful execution processed changes are deleted from the MV Log.

MLOG$_MY_TAB is the MV Log for MY_TAB created as:

Materialized Views

RUPD Tables

Fast Refreshable MVs

Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a complete refresh takes more time to complete than a fast refresh or a synchronous refresh.

 

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it.

Commit SCN-based materialized view logs use commit SCN data rather than timestamps, which removes the need for the setup operations and thus can improve the speed of the materialized view refresh.

If you specify the COMMIT SCN clause, then a commit SCN-based materialized view log is created.