Note
This view is introduced since OceanBase Database V4.3.0.
Purpose
The CDB_MVIEW_LOGS view displays information about all materialized view logs.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The ID of the tenant. |
| LOG_OWNER | varchar(128) | NO | The owner of the materialized view log. |
| MASTER | varchar(128) | NO | The name of the master table to which the materialized view log belongs. |
| LOG_TABLE | varchar(128) | NO | The name of the materialized view log table. |
| LOG_TRIGGER | varchar(128) | YES | The AFTER row trigger on the master table for inserting rows into the materialized view log.
NoteThis column is used only for compatibility and is |
| ROWIDS | varchar(3) | NO | Indicates whether ROWIDs are recorded. Valid values:
|
| PRIMARY_KEY | varchar(3) | NO | Indicates whether the primary key is recorded. Valid values:
|
| OBJECT_ID | varchar(3) | NO | Indicates whether object IDs are recorded in the object table. Valid values:
NoteThis column is used only for compatibility and is |
| FILTER_COLUMNS | varchar(3) | NO | Indicates whether filter columns are recorded. Valid values:
|
| SEQUENCE | varchar(3) | NO | Indicates whether sequence values that provide additional sorting information are recorded. Valid values:
NoteAt present, the value of this column is fixed to |
| INCLUDE_NEW_VALUES | varchar(3) | NO | Indicates whether both old values and new values are recorded. Valid values:
NoteAt present, the value of this column is fixed to |
| PURGE_ASYNCHRONOUS | varchar(3) | NO | Indicates whether the materialized view log is asynchronously purged. Valid values:
|
| PURGE_DEFERRED | varchar(3) | NO | Indicates whether the materialized view log is purged after a delay. Valid values:
|
| PURGE_START | datetime | YES | The time when the purge starts. |
| PURGE_INTERVAL | varchar(200) | YES | The interval for purging. |
| LAST_PURGE_DATE | datetime | YES | The date when the materialized view log was last purged. |
| LAST_PURGE_STATUS | bigint(1) | NO | The status of the last purge of the materialized view log. Valid values:
NoteAt present, the value of this column is fixed to |
| NUM_ROWS_PURGED | bigint(20) | YES | The number of rows purged in the last purge of the materialized view log. |
| COMMIT_SCN_BASED | varchar(3) | NO | Indicates whether the materialized view log was committed based on the SCN. Valid values:
NoteAt present, the value of this column is fixed to |
| STAGING_LOG | varchar(3) | NO | Indicates whether the materialized view log is a temporary log that is synchronously refreshed. Valid values:
NoteThis column is used only for compatibility and is |
Sample query
obclient [test_db]> SELECT * FROM oceanbase.CDB_MVIEW_LOGS;
The query result is as follows:
+-----------+-----------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+---------------------+------------------+---------------------+-------------------+-----------------+------------------+-------------+
| TENANT_ID | 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 |
+-----------+-----------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+---------------------+------------------+---------------------+-------------------+-----------------+------------------+-------------+
| 1002 | test_db | t1 | mlog$_t1 | NULL | NO | YES | NO | YES | YES | YES | NO | NO | NULL | NULL | 2024-01-18 15:28:52 | 0 | 0 | YES | NO |
| 1002 | test_db | test_tbl1 | mlog$_test_tbl1 | NULL | NO | YES | NO | YES | YES | YES | NO | NO | NULL | NULL | 2024-01-18 10:16:43 | 0 | 0 | YES | NO |
| 1004 | SYS | TEST_TBL1 | MLOG$_TEST_TBL1 | NULL | NO | YES | NO | YES | YES | YES | NO | YES | 2024-01-19 10:21:20 | current_date + 1 | 2024-01-19 10:21:20 | 0 | 0 | YES | NO |
+-----------+-----------+-----------+-----------------+-------------+--------+-------------+-----------+----------------+----------+--------------------+--------------------+----------------+---------------------+------------------+---------------------+-------------------+-----------------+------------------+-------------+
3 rows in set (0.123 sec)