1. Using Oracle Database Actions for On-Premises Oracle Database
  2. The SQL and Data Modeler Pages
  3. Creating and Editing Database Objects
  4. The Materialized View Log Properties Dialog

3.4.6 The Materialized View Log Properties Dialog

The Materialized View Log Properties dialog is displayed when you create or edit a materialized view log, which is a table associated with the master table of a materialized view.

To create a materialized view log for a selected schema, in SQL, in the Navigator tab, select Materialized View Logs from the object type drop-down list, click Object submenu Object submenu, and select Create Object.

To edit, right-click a materialized view log object in the Navigator pane and select Edit.

Schema: Database schema in which to create the materialized view log.

Table: Name of the master table of the materialized view to be associated with this materialized view log.

Properties tab

  • Row ID Logged: Yes indicates that the rowid of all rows changed should be recorded in the materialized view log. No indicates that the rowid of all rows changed should not be recorded in the materialized view log.

  • PK Logged: Yes indicates that the primary key of all rows changed should be recorded in the materialized view log; No indicates that the primary key of all rows changed should not be recorded in the materialized view log.

  • New values: Yes saves both old and new values for update DML operations in the materialized view log; No disables the recording of new values in the materialized view log. If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, you must specify Yes.

  • Object ID Logged: For a log on an object table only: Yes indicates that the system-generated or user-defined object identifier of every modified row should be recorded in the materialized view log. No indicates that the system-generated or user-defined object identifier of every modified row should not be recorded in the materialized view log.

  • Cache: For data that will be accessed frequently, CACHE specifies that the blocks retrieved for this log are placed at the most recently used end of the least recently used list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. NOCACHE specifies that the blocks are placed at the least recently used end of the LRU list.

  • Parallel: If YES, parallel operations will be supported for the materialized view log.

  • Sequence Logged: Yes indicates that a sequence value providing additional ordering information should be recorded in the materialized view log. No indicates that a sequence value providing additional ordering information should not be recorded in the materialized view log. Sequence numbers (that is, Yes for this option) are necessary to support fast refresh after some update scenarios.

  • Commit SCN: If this option is enabled, the database is instructed to use commit SCN data rather than timestamps.

  • Available Columns and Selected Columns: Additional columns, which are non-primary-key columns referenced by subquery materialized views, to be recorded in the materialized view log. To select one or more filter columns, use the arrow buttons to move columns from Available to Selected.

Storage tab

  • Tablespace: Tablespace in which the materialized view log is to be created

  • Logging: YES or NO, to establish the logging characteristics for the materialized view log.

  • Buffer Mode: Select KEEP to put blocks from the segment into the KEEP buffer pool. Select RECYCLE to put blocks from the segment into the RECYCLE pool. Select DEFAULT to indicate the default buffer pool.

  • Percent Free: Specify a whole number representing the percentage of space in each data block of the database object reserved for future updates to rows of the object. The value of PCTFREE must be a value from 0 to 99.

  • Percent Used: Specify a whole number representing the minimum percentage of used space that Oracle maintains for each data block of the database object. PCTUSED is specified as a positive integer from 0 to 99 and defaults to 40.

  • Initrans: Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1.

  • Freelists: In tablespaces with manual segment-space management, for objects other than tablespaces and rollback segments, specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list.

  • Freelist Groups: In tablespaces with manual segment-space management, specify the number of groups of free lists for the database object you are creating.

  • Initial Extent: Specify the size of the first extent of the object.

  • Next Extent: Specify in bytes the size of the next extent to be allocated to the object.

  • Percent Increase: In locally managed tablespaces, Oracle Database uses the value of PCTINCREASE during segment creation to determine the initial segment size and ignores this parameter during subsequent space allocation.

  • Min Extent: In locally managed tablespaces, Oracle Database uses the value of MINEXTENTS in conjunction with PCTINCREASE, INITIAL and NEXT to determine the initial segment size.

  • Max Extent: This storage parameter is valid only for objects in dictionary-managed tablespaces. Specify the total number of extents, including the first, that Oracle can allocate for the object.

  • Unlimited: Select this option if you want extents to be allocated automatically as needed. Oracle recommends this setting as a way to minimize fragmentation.

Purge tab

  • Type: In IMMEDIATE SYNCHRONOUS, the materialized view log is purged immediately after refresh. This is the default. In IMMEDIATE ASYNCHRONOUS, the materialized view log is purged in a separate Oracle Scheduler job after the refresh operation.

  • Deferred, Start With, Next, Repeat Interval: Sets up a scheduled purge that is independent of the materialized view refresh and is initiated during CREATE or ALTER MATERIALIZED VIEW LOG statement.

Refresh tab

  • Type: Synchronous Refresh creates a staging log that can be used for synchronous refresh. Specify the name of the staging log to be created. The staging log will be created in the schema in which the master table resides. Fast Refresh creates a materialized view log that can be used for fast refresh. The materialized view log will be created in the same schema in which the master table resides. This is the default.

DDL pane

Based on the inputs provided, the DDL statements are generated. You can review and save the SQL statements. If you want to make any changes, go back to the relevant pane and make the changes there.

  • For a new materialized view log, click CREATE to view the generated DDL statements.

  • When you edit a materialized view log, click UPDATE to view the generated ALTER statements. For a new materialized view log, the UPDATE tab will not be available.

When you are finished, click Apply.

Output pane

Displays the results of the DDL commands. If there are any errors, go to the respective pane, fix the errors, and run the commands again. You can save to a text file or clear the output.