A materialized view log (mlog) records the incremental data updates of a user table that is the base table of the materialized view, to support quick refresh of the materialized view. An mlog is a record table that tracks changes to the base table and applies these changes to the corresponding materialized view to achieve quick refresh of the materialized view.
Limitations
You can create a materialized view log only on a normal user table.
You can bind a base table to only one materialized view log.
When you create a materialized view log, if a transaction is running on the base table, the materialized view log create operation is blocked until the transaction is completed.
Materialized view logs do not support
LOBcolumns.Materialized view logs do not support the JSON, XML, GIS, or UDT data type.
Materialized view logs do not support generated columns, including virtual columns and non-virtual columns.
Materialized view logs do not support partitioning.
Like that of a normal table, the name of a materialized view log also cannot exceed 64 characters in length. In addition, the name of a materialized view log consists of a prefix
mlog$_and the name of the base table. Therefore, the name of the base table cannot exceed 58 characters in length.Materialized view logs do not support table-level restore.
Materialized view logs that are separately dropped are not moved to the recycle bin.
Materialized view logs do not support the
ALTERoperation.You cannot create indexes on materialized view logs.
Materialized view logs do not support DML operations.
User privileges
- You need the
SELECTprivilege on the base table and theCREATE TABLEprivilege to create a materialized view log. - You need the
DROP TABLEprivilege to drop a materialized view log. - A materialized view log only supports the
SELECTprivilege. Other DML operations are not supported.
Schema definition of a materialized view log
You can create only one materialized view log for a table. The schema name of the materialized view log is mlog$_table, in which table is the name of the base table.
The following table describes the schema definition of a materialized view log.
| Column | Type | Description |
|---|---|---|
| sequence$$ | in64_t | The primary key column of the materialized view log, which is an auto-increment column.
NoteThe primary key column of the materialized view log is a composite column that consists of the primary key of the base table, all partitioning keys (if any) of the base table, and the |
| primary key | Same as the base table | The primary key of the base table. If the base table has a composite primary key, the value contains multiple columns. |
| dmltype$$ | char(1) | The DML operation type. Valid values are I, D, and U, which indicate INSERT, DELETE, and UPDATE, respectively. |
| old_new$$ | char(1) | Indicates whether the value is an old or new value in an UPDATE statement. For each UPDATE operation on one data row in the base table, the system writes two rows of data to the materialized view log. The row with the old value is marked with O. The row with the new value is marked with N. |
| column 1 | Same as the base table | The normal column 1 in the base table. |
| … | N/A | N/A |
| column N | Same as the base table | The normal column N in the base table. |
| ora_rowscn | N/A | The pseudocolumn, which is recorded in a hidden column at the storage layer and can be read. |
| m_row$$ | uint64_t | The hidden primary key of the base table. The materialized view log contains this column only if the base table does not have a primary key. |
Use an existing materialized view log
- You can directly query the schema and data of a materialized view log.
- You can use the
DBMS_MVIEW.PURGE_LOG(table_name)statement to purge a materialized view log. - When the size of a materialized view log exceeds the available disk space, an error is reported. In this case, you must drop the materialized view log and create a new one.
Impact of base table operations on the materialized view log
DML operations on the base table
The materialized view log of a base table records the following DML operations on the base table: INSERT, DELETE, and UPDATE.
- If you perform an
INSERToperation on the base table, the system inserts a record to the materialized view log for each inserted row of data. In the record, thedmltype$$column value isI, and theold_new$$column value isN. - If you perform a
DELETEoperation on the base table, the system inserts a record to the materialized view log for each deleted row of data. In the record, thedmltype$$column value isD, and theold_new$$column value isO. - If you perform an
UPDATEoperation on the base table, the system inserts two records to the materialized view log for each modified row of data. In the first record, the old value before theUPDATEoperation is recorded, thedmltype$$column value isU, and theold_new$$column value isO. In the second record, the new value after theUPDATEoperation is recorded, thedmltype$$column value isU, and theold_new$$column value isN.
DDL operations on the base table
Before you drop a base table, you need to drop its materialized view log first. Otherwise, an error is reported. This is because the materialized view log is bound to the base table.
For more information about the DDL operations supported by base tables, see Online and offline DDL operations.