ALTER MATERIALIZED VIEW LOG

Purpose

Use the ALTER MATERIALIZED VIEW LOG statement to alter the storage characteristics, refresh mode or time, or type of an existing materialized view log. A materialized view log is a table associated with the master table of a materialized view.

Note: The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.

See Also: ALTER MATERIALIZED VIEW for more information on materialized views, including refreshing them CREATE MATERIALIZED VIEW for a description of the various types of materialized views

Prerequisites

Only the owner of the master table or a user with the SELECT privilege for the master table can alter a materialized view log.

See Also: Oracle8i Replication for detailed information about the prerequisites for ALTER MATERIALIZED VIEW LOG

Syntax

physical_attributes_clause::=

storage_clause: See storage_clause.

partitioning_clauses: See ALTER TABLE.

allocate_extent_clause::=

parallel_clause::=

Keywords and Parameters

schema

Specify the schema containing the master table. If you omit schema, Oracle assumes the materialized view log is in your own schema.

table

Specify the name of the master table associated with the materialized view log to be altered.

physical_attributes_clause

The physical_attributes_clause lets you change the value of PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters for the table, the partition, the overflow data segment, or the default characteristics of a partitioned table.

See Also: CREATE TABLE and the "Materialized View Storage Example" for a description of these parameters

partitioning_clauses

The syntax and general functioning of the partitioning clauses is the same as for the ALTER TABLE statement

Restrictions:

• You cannot use the LOB_storage_clause or modify_LOB_storage_clause when modifying a materialized view log.

• If you attempt to drop, truncate, or exchange a materialized view log partition, Oracle raises an error.

  See Also: ALTER TABLE

parallel_clause

The parallel_clause lets you specify whether parallel operations will be supported for the materialized view log.

Note: The syntax of the parallel_clause supersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility, but may result in slightly different behavior.

NOPARALLEL	Specify NOPARALLEL for serial execution. This is the default.
PARALLEL	Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
PARALLEL integer	Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

LOGGING | NOLOGGING

Specify the logging attribute of the materialized view log.

See Also: ALTER TABLE for information about specifying this attribute

allocate_extent_clause

The allocate_extent_clause lets you explicitly allocate a new extent for the materialized view log.

See Also: ALTER TABLE

CACHE | NOCACHE

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 LRU 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.

See Also: ALTER TABLE for information about specifying CACHE or NOCACHE

ADD

The ADD clause lets you augment the materialized view log so that it records the primary key values or rowid values when rows in the materialized view master table are updated. This clause can also be used to record additional filter columns.

To stop recording any of this information, you must first drop the materialized view log and then re-create it. Dropping the materialized view log and then re-creating it forces each of the existing materialized views that depend on the master table to complete refresh on its next refresh.

PRIMARY KEY	Specify PRIMARY KEY if you want the primary-key values of all rows that are updated to be recorded in the materialized view log.
ROWID	Specify ROWID if you want the rowid values of all rows that are updated to be recorded in the materialized view log.
filter_column(s)	Specify the columns whose values you want to be recorded in the materialized view log for all rows that are updated. Filter columns are non-primary-key columns referenced by materialized views.

Restriction: You can specify only one PRIMARY KEY, one ROWID, and one filter column list per materialized view log. Therefore, if any of these three values were specified at create time (either implicitly or explicitly), you cannot specify those values in this ALTER statement.

See Also: CREATE MATERIALIZED VIEW for information on explicit and implicit inclusion of materialized view log values Oracle8i Replication for more information about filter columns

NEW VALUES

The NEW VALUES clause lets you specify whether Oracle saves both old and new values in the materialized view log. The value you set in this clause applies to all columns in the log, not only to primary key, rowid, or filter columns you may have added in this statement.

INCLUDING	Specify INCLUDING to save both new and old values in the 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 INCLUDING.
EXCLUDING	Specify EXCLUDING to disable the recording of new values in the log. You can use this clause to avoid the overhead of recording new values. However, do not use this clause if you have a fast-refreshable single-table materialized aggregate view defined on this table.

Examples

Materialized View Storage Example

The following statement changes the MAXEXTENTS value of a materialized view log:

ALTER MATERIALIZED VIEW LOG ON dept  
   STORAGE MAXEXTENTS 50;

PRIMARY KEY Example

The following statement alters an existing rowid materialized view log to also record primary key information:

ALTER MATERIALIZED VIEW LOG ON sales 
   ADD PRIMARY KEY;