Materialized view logs|V4.4.2| docs|Distributed Database

Materialized view logs

Last Updated：2026-03-06 07:02:42 Updated

A materialized view log (mlog) records incremental updates to the base table to support fast refreshes of materialized views. An mlog is a record table that tracks changes to the base table and applies these changes to the corresponding materialized view for fast refreshes.

If the automatic management of materialized view logs is enabled, OceanBase Database automatically creates or updates the mlog definition when you create a materialized view with incremental refresh or real-time refresh. For more information about the automatic management of materialized view logs, see Automatic management of materialized view logs.

Limitations

You can create a materialized view log only on a regular table or a materialized view.
A regular table can be bound to only one materialized view log.
When you create a materialized view log, if a transaction is running on the regular table, the creation operation is blocked until the transaction ends.
A materialized view log supports LOB columns, but only supports inline LOB storage. For more information about LOB columns, see LOB.
A materialized view log does not support JSON, XML, GIS, or UDT data types.
A materialized view log does not support generated columns (including virtual and non-virtual columns).
A materialized view log does not support specifying partitions. The partitions of the materialized view log are bound to those of the regular table.
The maximum length of a materialized view log name is the same as that of a regular table, which is 64 characters. The name of the regular table that is used to create a materialized view log cannot exceed 58 characters because the name of the materialized view log is prefixed with mlog$_.
A materialized view log does not support table-level recovery.
When you delete a materialized view log separately, it does not go to the recycle bin.
After a materialized view log is created, you cannot perform an ALTER operation on it.
You cannot create an index on a materialized view log.
You cannot perform DML operations on a materialized view log. An error will be returned.

Privileges

To create a materialized view log, you must have the SELECT and CREATE TABLE privileges on the regular table.
To modify a materialized view log, you must have the ALTER privilege on the regular table.
To drop a materialized view log, you must have the DROP TABLE privilege.
You can only grant the SELECT privilege on a materialized view log. You cannot perform other DML operations on it.

Schema definition of a materialized view log

A regular table can have only one materialized view log. The schema name of the materialized view log is mlog$_table, where table is the name of the regular table.

The schema definition of a materialized view log is as follows:

Column name	Type	Description
sequence$$	in64_t	An auto-increment column that serves as the primary key of the materialized view log (mlog). Note The primary key of mlog consists of the primary key of the regular table, all partitioning keys (if any), and the auto-increment column `sequence$$`. These columns together form a composite primary key.
primary key	Follows the regular table	If the regular table has a primary key, the primary key columns of the regular table (which may include multiple columns for a composite primary key) are recorded in mlog.
dmltype$$	char(1)	Records the DML type. The value can be `I`, `D`, or `U`, which indicate `INSERT`, `DELETE`, and `UPDATE`, respectively.
old_new$$	char(1)	Marks the old and new values in an `UPDATE` statement. An `UPDATE` statement writes two rows of data to the materialized view log: one row for the old value before the `UPDATE` statement and another row for the new value after the `UPDATE` statement. The old value is marked with `O`, and the new value is marked with `N`.
column 1	Follows the regular table	Column 1 of the regular table.
...	N/A	N/A
column N	Follows the regular table	Column N of the regular table.
ora_rowscn	N/A	A pseudo column that records a hidden column in the storage layer. You can read this column.
m_row$$	uint64_t	This column is recorded in mlog only when the regular table has no primary key. mlog must contain the primary key columns of the regular table. If the regular table has no primary key, the name of the hidden primary key in mlog is `M_ROW$$`.

Operations on an existing materialized view log

You can directly query the schema and data of the materialized view log.
You can execute the PURGE operation on a materialized view log of a regular table by using DBMS_MVIEW.PURGE_LOG(table_name).
If the size of the materialized view log exceeds the available disk space, an error is returned. In this case, you must delete the materialized view log and recreate it before you can use it again.

Impact of operations on the regular table on the materialized view log

DML operations on the regular table

The schema definition of the materialized view log is used to record DML operations on the regular table. Therefore, the INSERT, DELETE, and UPDATE operations on the regular table are recorded in the materialized view log, as follows:

When you perform an INSERT operation on the regular table, the inserted rows are also recorded in the materialized view log. The dmltype$$ column of each record is I, and the old_new$$ column is N.
When you perform a DELETE operation on the regular table, the deleted rows are also recorded in the materialized view log. The dmltype$$ column of each record is D, and the old_new$$ column is O.
When you perform an UPDATE operation on the regular table, the modified rows are also recorded in the materialized view log. The first row records the old value of the row before the UPDATE operation. The dmltype$$ column of this row is U, and the old_new$$ column is O. The second row records the new value of the row after the UPDATE operation. The dmltype$$ column of this row is U, and the old_new$$ column is N.

DDL operations on the regular table

Before you delete the regular table, you must delete the corresponding materialized view log. Otherwise, an error is returned. This is because a materialized view log is bound to a regular table. Therefore, you cannot directly delete the regular table and retain the materialized view log.

For more information about DDL operations supported by the regular table, see Online DDL and Offline DDL Operations.

Create a materialized view log

Note

OceanBase Database does not support specifying partitions (Partition) for mlog. The partitions of mlog are bound to those of the base table.

Privilege requirements

To create a materialized view log, you must have the CREATE TABLE and SELECT privileges on the base table. For more information about the privileges of OceanBase Database, see Privilege types in Oracle-compatible mode.

Syntax

The syntax of the SQL statement for creating a materialized view log is as follows:

CREATE [OR REPLACE] MATERIALIZED VIEW LOG ON [schema.] table_name
    [parallel_clause]
    [with_clause]
    [mv_log_purge_clause];

Parameters:

OR REPLACE: optional. If you specify this clause, OceanBase Database creates a new mlog with the same definition as the existing mlog and replaces the existing mlog with the new one. During the recreation of the mlog, the read/write operations on the materialized view and base table are not affected.
table_name: the name of the base table corresponding to the materialized view log.
parallel_clause: optional. Specifies the parallelism for clearing the materialized view log.
with_clause: optional. Specifies the auxiliary columns in the materialized view log.
mv_log_purge_clause: optional. Specifies the time when the data in the materialized view log is cleared.

For more information about the parameters in the syntax, see CREATE MATERIALIZED VIEW LOG.

Here is an example:

Create a table named tbl1.

CREATE TABLE tbl1 (col1 NUMBER, col2 VARCHAR2(20), col3 NUMBER, PRIMARY KEY(col1, col3))
    PARTITION BY HASH(col3) PARTITIONS 10;

Create a materialized view log on the tbl1 table. Specify the parallelism for clearing the materialized view log as 5 and record the change information of the col2 column in the materialized view log. The change information includes the new values before and after the change. Configure the materialized view log to clear expired records every 1 day starting from the current date.
```
CREATE MATERIALIZED VIEW LOG ON tbl1 
  PARALLEL 5 
  WITH SEQUENCE(col2) INCLUDING NEW VALUES
  PURGE START WITH current_date NEXT current_date + 1;
```

View the information of the materialized view log on the tbl1 table.

DESC mlog$_tbl1;

The return result is as follows:

+------------+--------------+------+------+---------+-------+
| FIELD      | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+------------+--------------+------+------+---------+-------+
| COL1       | NUMBER       | NO   | PRI  | NULL    | NULL  |
| COL2       | VARCHAR2(20) | YES  | NULL | NULL    | NULL  |
| COL3       | NUMBER       | NO   | PRI  | NULL    | NULL  |
| SEQUENCE$$ | BIGINT(20)   | NO   | PRI  | NULL    | NULL  |
| DMLTYPE$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
| OLD_NEW$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
+------------+--------------+------+------+---------+-------+
6 rows in set

Modify a materialized view log

Privilege requirements

To execute the ALTER MATERIALIZED VIEW LOG statement, you must have the ALTER privilege on the base table. For more information about the privileges of OceanBase Database, see Privilege types in Oracle-compatible mode.

Syntax

The syntax of the SQL statement for modifying a materialized view log is as follows:

ALTER MATERIALIZED VIEW LOG ON [schema.]table_name alter_mlog_action_list;

alter_mview_action_list:
    alter_mlog_action [, alter_mlog_action ...]

alter_mlog_action:
    parallel_clause
    | PURGE [[START WITH expr] [NEXT expr]]

parallel_clause:
    NOPARALLEL 
    | PARALLEL integer

Parameters:

schema.: optional. The schema where the base table of the materialized view log is located. If you do not specify this clause, the base table is located in the schema of the current session by default.
table_name: the name of the base table corresponding to the materialized view log.
alter_mlog_action_list: the list of operations that can be performed on the materialized view log. You can specify multiple operations separated by commas (,).

For more information about the parameters in the syntax, see ALTER MATERIALIZED VIEW LOG.

Here is an example:

Create a table named test_tbl1.

CREATE TABLE test_tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER, col4 BLOB);

Create a materialized view log on the test_tbl1 table.

CREATE MATERIALIZED VIEW LOG ON test_tbl1 
    WITH SEQUENCE(col2, col3, col4) INCLUDING NEW VALUES;

Change the parallelism of the materialized view log on the test_tbl1 table to 5.
```
ALTER MATERIALIZED VIEW LOG ON test_tbl1 PARALLEL 5;
```
Configure the materialized view log on the test_tbl1 table to clear expired records every 1 day starting from the current date.
```
ALTER MATERIALIZED VIEW LOG ON test_tbl1
    PURGE START WITH current_date NEXT current_date + 1;
```

Drop a materialized view log

Considerations

If a transaction is in progress on the base table, the drop operation is blocked until the transaction is completed.
When you drop a materialized view log separately, it does not go to the recycle bin.

Privilege requirements

To drop a materialized view log, you must have the DROP TABLE privilege. For more information about the privileges of OceanBase Database, see Privilege types in Oracle-compatible mode.

Syntax

The syntax of the SQL statement for dropping a materialized view log is as follows:

DROP MATERIALIZED VIEW LOG ON [schema.] table;

Parameters:

schema.: optional. The schema where the base table of the materialized view log is located. If you do not specify this clause, the base table is located in your schema by default.
table: the name of the base table corresponding to the materialized view log.

Here is an example:

Drop the materialized view log on the tbl1 table.

DROP MATERIALIZED VIEW LOG ON tbl1;

Example

This example shows how to create a regular table, a materialized view log, and an incrementally refreshed materialized view. It also covers how to delete a materialized view log and an incrementally refreshed materialized view.

Create a table named test_tbl1.

CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 INT, col3 INT);

Create a materialized view log on the test_tbl1 table, specifying that changes should be identified by a sequence number (SEQUENCE). The columns to be recorded include col2 and col3.
```
CREATE MATERIALIZED VIEW LOG ON test_tbl1
    WITH SEQUENCE (col2, col3) INCLUDING NEW VALUES;
```
Create a materialized view named mv_test_tbl1, specifying that it should be incrementally refreshed and automatically refreshed every 5 minutes. In the query part, specify that the data should be grouped by the col2 column, and calculate the number of records in each group (cnt), the number of non-null records in the col3 column (cnt_col3), and the sum of the col3 column (sum_col3) as the results of the materialized view.
```
CREATE MATERIALIZED VIEW mv_test_tbl1
  REFRESH FAST ON DEMAND
  START WITH current_date NEXT current_date + interval '5' minute
  AS SELECT col2, COUNT(*) cnt, COUNT(col3) cnt_col3, SUM(col3) sum_col3 
    FROM test_tbl1 
    GROUP BY col2;
```

View the log information of the materialized view log for the test_tbl1 table.

SELECT * FROM sys.DBA_MVIEW_LOGS WHERE MASTER = 'TEST_TBL1';

Notice

In Oracle-compatible mode, when the MASTER field in the sys.DBA_MVIEW_LOGS view matches the table name, the table name must be in uppercase.

The returned result is as follows:

+--------------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+-----------------+-------------------+-----------------+------------------+-------------+-----------+-----------------+
| LOG_OWNER    | MASTER    | LOG_TABLE       | LOG_TRIGGER | ROWIDS | PRIMARY_KEY | OBJECT_ID | FILTER_COLUMNS | SEQUENCE | INCLUDE_NEW_VALUES | PURGE_ASYNCHRONOUS | PURGE_DEFERRED | PURGE_START | PURGE_INTERVAL | LAST_PURGE_DATE | LAST_PURGE_STATUS | NUM_ROWS_PURGED | COMMIT_SCN_BASED | STAGING_LOG | PURGE_DOP | LAST_PURGE_TIME |
+--------------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+-----------------+-------------------+-----------------+------------------+-------------+-----------+-----------------+
| TEST_USER001 | TEST_TBL1 | MLOG$_TEST_TBL1 | NULL        | NO     | YES         | NO        | YES            | YES      | YES                | NO                 | NO             | NULL        | NULL           | 03-SEP-25       |                 0 |               0 | YES              | NO          |         1 |               0 |
+--------------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+-------------+----------------+-----------------+-------------------+-----------------+------------------+-------------+-----------+-----------------+
1 row in set

Delete the materialized view log for the test_tbl1 table.
```
DROP MATERIALIZED VIEW LOG ON test_tbl1;
```
Delete the materialized view mv_test_tbl1.
```
DROP MATERIALIZED VIEW mv_test_tbl1;
```

Materialized view logs

Limitations

Privileges

Schema definition of a materialized view log

Note

Operations on an existing materialized view log

Impact of operations on the regular table on the materialized view log

DML operations on the regular table

DDL operations on the regular table

Create a materialized view log

Note

Privilege requirements

Syntax

Modify a materialized view log

Privilege requirements

Syntax

Drop a materialized view log

Considerations

Privilege requirements

Syntax

Example

Notice

References