Creating Materialized View Logs

Before creating materialized view groups and materialized views for a remote materialized view site, create the necessary materialized view logs at the master site. A materialized view log is necessary for every master table that supports at least one materialized view with fast refreshes. You should create these materialized view logs regardless of whether the materialized view site is created manually or by using a deployment template.

To create a materialized view log at the master site:

1. In Enterprise Manager, connect to the Database Instance Home page as the replication administrator.

2. Click Data Movement.

3. Under the Advanced Replication section, click Manage. The Advanced Replication: Administration page appears.

4. Under Materialized View Replication, Master Site section, click the number next to Materialized View Logs to open the Materialized View Logs page.

5. On the Materialized View Logs page, click Create.

   The Create Materialized View Log page appears.

6. Specify the appropriate table from a schema in the Schema.Table field.

   To search for the table in the specified schema that contains the table for which you want to create a materialized view log, click the Search icon next to the field.

   • Specify the schema in the Schema field that contains the table for which you want to create a materialized view log.

   • Select the table for which you want to create a materialized view log from the Table list.

7. Click Populate Columns to display the columns available in the selected table in the Available Columns list in the Filter Columns section.

8. Select the tablespace where you want to store the materialized view log from the Tablespace list.

   Click the Search icon next to the field to search for the appropriate tablespace.

9. Select the appropriate Refresh Types:

   Row ID: Enable this box to allow the selected materialized view to use ROWIDs when refreshing. ROWID materialized views may be used for materialized views based on master tables that do not have a primary key, or for materialized views that do not include all primary key columns of the master tables.

   Primary Key: Enable this box to allow the selected materialized view to use primary key when refreshing. This option is the default.

   Note: If the master table does not have a primary key, then the Primary Key box is disabled and the Row ID box is selected by default. If your materialized view log must support both Row ID and Primary Key materialized views, be sure that you enable both the Row ID and the Primary Key boxes.

10. If necessary, enable the Include New Values box.

    When enabled, this box specifies that the materialized view log records both old and new values when a table update is made. Typically, you would only enable this option in a data warehousing environment with fast-refreshable single-table aggregates. Disable this option for a typical replication environment.

11. If necessary, enable the With Sequence Value box.

    When enabled, this box specifies that a sequence value providing additional ordering information should be recorded in the materialized view log. If you plan to have aggregate materialized views, then sequence numbers are necessary to support fast refresh after some update scenarios. Typically, this option should only be enabled if you plan to have aggregate materialized views based on the master table in a data warehousing environment. Disable this option for a typical replication environment.

12. If necessary, select any required filter columns (Filter Columns) from the Available Columns list to identify filter columns for materialized views that contain subqueries.

13. Click the right-arrow button to add the selected columns to the Filter Columns list.

14. If necessary, click Storage and Options subpage to modify the storage settings and options for your materialized view log.

    Click Help on either of these tabs to see additional information about the available storage settings and options.

15. Click OK to complete the creation of your materialized view log.