Note
This view is available starting with V4.3.0.
Purpose
The DBA_MVIEWS view is used to display information about all materialized views.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| OWNER | varchar(128) | NO | The name of the materialized view owner. |
| MVIEW_NAME | varchar(128) | NO | The name of the materialized view. |
| CONTAINER_NAME | varchar(128) | NO | The name of the materialized view container table. |
| QUERY | longtext | NO | The query that defines the materialized view. |
| QUERY_LEN | bigint(20) | NO | The length of the query, in bytes. |
| UPDATABLE | varchar(1) | NO | Indicates whether the materialized view is updatable.
NoteThis column is compatible only, and its value defaults to N. |
| UPDATE_LOG | varchar(128) | NO | The filename of the update log for updatable materialized views.
NoteThis column is compatible only, and its value defaults to NULL. |
| MASTER_ROLLBACK_SEG | varchar(128) | NO | The rollback segment for the master site or the master materialized view site.
NoteThis column is compatible only, and its value defaults to NULL. |
| MASTER_LINK | varchar(128) | NO | The database link for the master site or the master materialized view site.
NoteThis column is compatible only, and its value defaults to NULL. |
| REWRITE_ENABLED | varchar(1) | NO | Indicates whether rewrite is enabled.
|
| REWRITE_CAPABILITY | varchar(9) | NO | Indicates whether the materialized view meets the requirements for rewrite. If yes, it must follow these rules:
NoteThis column is compatible only, and its value defaults to NULL. |
| REFRESH_MODE | varchar(6) | NO | The refresh mode of the materialized view.
|
| REFRESH_METHOD | varchar(8) | NO | The default method for refreshing the materialized view (which can be overridden by an API).
|
| BUILD_MODE | varchar(9) | NO | How the materialized view is populated during creation.
|
| FAST_REFRESHABLE | varchar(18) | NO | Indicates whether the materialized view is suitable for incremental (fast) refresh.
NoteThis column is compatible only, and its value defaults to NULL. |
| LAST_REFRESH_TYPE | varchar(8) | NO | The method used for the most recent refresh.
|
| LAST_REFRESH_DATE | datetime | YES | The date when the materialized view was last refreshed. |
| LAST_REFRESH_END_TIME | datetime | NO | The end time of the most recent refresh of the materialized view. |
| STALENESS | varchar(19) | NO | The relationship between the content of the materialized view and the content of the materialized view master.
NoteThis column is compatible only, and its value defaults to NULL. |
| AFTER_FAST_REFRESH | varchar(19) | NO | The STALENESS value that appears after a fast refresh is applied to this materialized view. It has the same values as the STALENESS column. If the value is NA, it is used when a fast refresh is not applicable to this materialized view.
NoteThis column is compatible only, and its value defaults to NULL. |
| UNKNOWN_PREBUILT | varchar(1) | NO | Indicates whether the materialized view is prebuilt.
NoteThis column is compatible only, and its value defaults to N. |
| UNKNOWN_PLSQL_FUNC | varchar(1) | NO | Indicates whether the materialized view contains PL/SQL functions.
NoteThis column is compatible only, and its value defaults to N. |
| UNKNOWN_EXTERNAL_TABLE | varchar(1) | NO | Indicates whether the materialized view contains external tables.
NoteThis column is compatible only, and its value defaults to N. |
| UNKNOWN_CONSIDER_FRESH | varchar(1) | NO | Indicates whether the materialized view is considered fresh.
NoteThis column is for compatibility only. Its default value is N. |
| UNKNOWN_IMPORT | varchar(1) | NO | Indicates whether the materialized view is imported.
NoteThis column is for compatibility only. Its default value is N. |
| UNKNOWN_TRUSTED_FD | varchar(1) | NO | Indicates whether the materialized view is refreshed by using trusted constraints.
NoteThis column is for compatibility only. Its default value is N. |
| COMPILE_STATE | varchar(19) | NO | The validity of the materialized view relative to the objects it depends on.
NoteThis column is for compatibility only. Its default value is NULL. |
| USE_NO_INDEX | varchar(1) | NO | Indicates whether the materialized view is created by using the USING NO INDEX clause (Y) or the default index (N). The USING NO INDEX clause prohibits the creation of the default index.
NoteThis column is for compatibility only. Its default value is Y. |
| STALE_SINCE | datetime | NO | The start time when the materialized view becomes stale.
NoteThis column is for compatibility only. Its default value is NULL. |
| NUM_PCT_TABLES | bigint(0) | NO | The number of PCT base tables.
NoteThis column is for compatibility only. Its default value is NULL. |
| NUM_FRESH_PCT_REGIONS | bigint(0) | NO | The number of fresh PCT partition regions.
NoteThis column is for compatibility only. Its default value is NULL. |
| NUM_STALE_PCT_REGIONS | bigint(0) | NO | The number of stale PCT partition regions.
NoteThis column is for compatibility only. Its default value is NULL. |
| SEGMENT_CREATED | varchar(3) | NO | Indicates whether the materialized view is created by using the SEGMENT CREATION DEFERRED clause.
NoteThis column is for compatibility only. Its default value is NO. |
| EVALUATION_EDITION | varchar(128) | NO | The version name of the version object referenced in the expression column.
NoteThis column is for compatibility only. Its default value is NULL. |
| UNUSABLE_BEFORE | varchar(128) | NO | The name of the oldest version in which the stored results of the materialized view subquery can be used for query rewriting. In versions earlier than the specified version, the stored results of the materialized view data are considered unavailable. If no such version is specified, the value is NULL.
NoteThis column is for compatibility only. Its default value is NULL. |
| UNUSABLE_BEGINNING | varchar(128) | NO | The name of the oldest version in which the stored results of the materialized view subquery cannot be used for query rewriting. In the specified version and any of its descendants, the data cannot be used for query rewriting. If no such version is specified, the value is NULL.
NoteThis column is for compatibility only. Its default value is NULL. |
| DEFAULT_COLLATION | varchar(100) | NO | The default collation of the materialized view.
NoteThis column is for compatibility only. Its default value is NULL. |
| ON_QUERY_COMPUTATION | varchar(1) | NO | Indicates whether the materialized view is a real-time materialized view.
|
| REFRESH_DOP | bigint(20) | NO | The parallelism of the background refresh of the materialized view. The default value is 0.
Note
|
| DATA_SYNC_SCN | bigint(20) unsigned | NO | The data SCN of the materialized view.
NoteThis column is available starting with OceanBase Database V4.3.5 BP2. |
| DATA_SYNC_DELAY | varchar(128) | NO | The data synchronization delay of the materialized view, in seconds.
NoteThis column is available starting with OceanBase Database V4.3.5 BP2. |
Sample query
Query all materialized views and display the first record.
obclient [(none)]> SELECT * FROM oceanbase.DBA_MVIEWS LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: db_test
MVIEW_NAME: mv_tbl1
CONTAINER_NAME: mv_tbl1
QUERY: select `db_test`.`tbl1`.`col1` AS `col1`,`db_test`.`tbl1`.`col2` AS `col2` from `db_test`.`tbl1` where (`db_test`.`tbl1`.`col3` >= 20)
QUERY_LEN: 134
UPDATABLE: N
UPDATE_LOG: NULL
MASTER_ROLLBACK_SEG: NULL
MASTER_LINK: NULL
REWRITE_ENABLED: N
REWRITE_CAPABILITY: NULL
REFRESH_MODE: DEMAND
REFRESH_METHOD: FORCE
BUILD_MODE: IMMEDIATE
FAST_REFRESHABLE: NULL
LAST_REFRESH_TYPE: COMPLETE
LAST_REFRESH_DATE: 2025-04-23 10:44:55
LAST_REFRESH_END_TIME: 2025-04-23 10:44:55
STALENESS: NULL
AFTER_FAST_REFRESH: NULL
UNKNOWN_PREBUILT: N
UNKNOWN_PLSQL_FUNC: N
UNKNOWN_EXTERNAL_TABLE: N
UNKNOWN_CONSIDER_FRESH: N
UNKNOWN_IMPORT: N
UNKNOWN_TRUSTED_FD: N
COMPILE_STATE: NULL
USE_NO_INDEX: Y
STALE_SINCE: NULL
NUM_PCT_TABLES: NULL
NUM_FRESH_PCT_REGIONS: NULL
NUM_STALE_PCT_REGIONS: NULL
SEGMENT_CREATED: NO
EVALUATION_EDITION: NULL
UNUSABLE_BEFORE: NULL
UNUSABLE_BEGINNING: NULL
DEFAULT_COLLATION: NULL
ON_QUERY_COMPUTATION: N
REFRESH_DOP: 0
DATA_SYNC_SCN: 1745376295576462000
DATA_SYNC_DELAY: 17
1 row in set