Materialized View Logs

Materialized view log is a table associated with the master table of a materialized view. When changes are made to master table data, Oracle Database stores those changes description in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table.

Setting materialized view log parameters

You cannot set materialized view log name and owner. Materialized view log name is always autogenerated by Oracle. It is ‘MLOG$_’ + name of the table, on which it is being created. It also has the same owner as the table, on which it is being created.

Materialized view log options determine what materialized view log should record when data in the master table is changed. One of the following options should be set when creating materialized view log.

  • Primary Key
  • Row ID
  • Object ID

Primary key option is automatically set by default when no other option is set. So to clear this option, select another one at first. Materialized view log can have Object ID option selected only when it is created for object table. Sequence option is vailable only when creating materialized view log on Oracle 10g and higher.

You can select additional table columns to record them in materialized view log for all changed rows of master table in the Columns grid. Note, that if primary key option is set, you can not select primary key columns. In that case they are automatically implicitly included.

Partitioning and storage parameters can be set just like corresponding parameters for the table.

Creating a Materialized View Log

  1. On the Standard toolbar, click New Database Object.
  2. Select the Materialized View Log type in the dialog box.
  3. Specify a table name and schema, on which materialized view log will be created.
  4. Set materialized view log options.
  5. Optionally, select columns to record them in materialized view log for all changed rows of master table in the Columns grid.
  6. Optionally, set storage and partitioning parameters on the corresponding tabs.
  7. Save the document.

Editing a Materialized View Log

You can edit materialized view by selecting Edit Materialized View Log from the shortcut menu. In the opened document you alter materialized view parameters. Save the document to apply changes you made. When editing materialized view log you can only select additional options, you can not deselect already selected options.