How to create materialized view log in Oracle?

A materialized view log is a table associated with the master table of a materialized view in Oracle.

After a change is made to the data in the master table, you might need to refresh the materialized view to update it with the new data. There are two refresh methods, a fast refresh and a complete refresh.

When you re-execute the materialized view query to refresh the materialized view, it’s a complete refresh.
If you want a fast refresh, a materialized view log will help storing every change to a row and refreshing the materialized view.

Details on refreshing the materialized view can be found in this post.

A master table can only have one materialized view log defined on it.

Syntax

Here is the syntax to create a materialized view log:

CREATE MATERIALIZED VIEW LOG ON table_name
    [WITH [PRIMARY KEY][,ROWID]|[ROWID][,PRIMARY KEY] [(columnName[,…])]];

You can define the list of columns for which changes will be recorded in the log.

Example

To create a materialized view log on the employees table to record changes on the email column:

CREATE MATERIALIZED VIEW LOG ON employees WITH (email);

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.