Overview
The DBA_MVIEWS view displays information about all materialized views.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| OWNER | varchar(128) | NO | Materialized View Owner |
| MVIEW_NAME | varchar(128) | NO | Materialized View Name |
| CONTAINER_NAME | varchar(128) | NO | Materialized View Container Table Name |
| QUERY | longtext | NO | Query for Defining a Materialized View |
| QUERY_LEN | bigint(20) | NO | Defines the length of the query, in bytes. |
| UPDATABLE | varchar(1) | NO | Indicates whether the materialized view is updatable.
NoteThis field is only compatible with the value N by default. |
| UPDATE_LOG | varchar(128) | NO | For updatable materialized views, the file name of the update log.
NoteThis field is only compatible and its default value is NULL. |
| MASTER_ROLLBACK_SEG | varchar(128) | NO | Rollback Segment of Primary Site or Primary Materialized View Site
NoteThis field is only compatible and its default value is NULL. |
| MASTER_LINK | varchar(128) | NO | Database link of the primary site or primary materialized view site
NoteThis field is only compatible and its default value is NULL. |
| REWRITE_ENABLED | varchar(1) | NO | Specifies whether to enable rewriting.
|
| REWRITE_CAPABILITY | varchar(9) | NO | Indicates whether the materialized view meets the rewrite conditions. If yes, the following rules must be followed:
NoteThis field is only compatible and its default value is NULL. |
| REFRESH_MODE | varchar(6) | NO | The refresh method of the materialized view. Valid values:
|
| REFRESH_METHOD | varchar(8) | NO | The default method for refreshing a materialized view. This can be overridden via an API.
|
| BUILD_MODE | varchar(9) | NO | How to populate the materialized view during creation.
|
| FAST_REFRESHABLE | varchar(18) | NO | Whether the materialized view supports incremental (fast) refresh.
NoteThis field is only compatible and its default value is NULL. |
| LAST_REFRESH_TYPE | varchar(8) | NO | Method used in the most recent refresh.
|
| LAST_REFRESH_DATE | datetime | YES | Date of Last Refresh of Materialized View |
| LAST_REFRESH_END_TIME | datetime | NO | End Time of Last Materialized View Refresh |
| STALENESS | varchar(19) | NO | The relationship between the contents of a materialized view and those of its master.
NoteThis field is only compatible, and its default value is NULL. |
| AFTER_FAST_REFRESH | varchar(19) | NO | Specifies the value of the STALENESS column that appears when this materialized view is subjected to a fast refresh. If the value is NA, it indicates that this value is used when fast refresh is not applicable to this materialized view.
NoteThis field is only compatible with the NULL value by default. |
| UNKNOWN_PREBUILT | varchar(1) | NO | Whether the materialized view is prebuilt.
NoteThis field is only compatible with the value N by default. |
| UNKNOWN_PLSQL_FUNC | varchar(1) | NO | Whether the materialized view contains PL/SQL functions.
NoteThis field is only compatible and its default value is N. |
| UNKNOWN_EXTERNAL_TABLE | varchar(1) | NO | Whether the materialized view contains external tables.
NoteThis field is only compatible and its default value is N. |
| UNKNOWN_CONSIDER_FRESH | varchar(1) | NO | Whether the materialized view is considered up to date.
NoteThis field is only compatible and its default value is N. |
| UNKNOWN_IMPORT | varchar(1) | NO | Whether the materialized view is imported.
NoteThis field is only compatible and its default value is N. |
| UNKNOWN_TRUSTED_FD | varchar(1) | NO | Whether the materialized view is refreshed using trusted constraints.
NoteThis field is only compatible and its default value is N. |
| COMPILE_STATE | varchar(19) | NO | Validity of the materialized view relative to its dependencies.
NoteThis column is only compatible with NULL values by default. |
| USE_NO_INDEX | varchar(1) | NO | Whether the materialized view is created using the USING NO INDEX clause (Y) or the default index (N). The USING NO INDEX clause prevents the creation of a default index.
NoteThis field is only compatible and its default value is Y. |
| STALE_SINCE | datetime | NO | Start Time When the Materialized View Becomes Obsolete
NoteThis field is only compatible and its default value is NULL. |
| NUM_PCT_TABLES | bigint(0) | NO | Number of PCT Base Tables
NoteThis field is only compatible and its default value is NULL. |
| NUM_FRESH_PCT_REGIONS | bigint(0) | NO | Number of Latest PCT Partition Regions
NoteThis field is only compatible and its default value is NULL. |
| NUM_STALE_PCT_REGIONS | bigint(0) | NO | Number of Old PCT Partitioned Areas
NoteThis field is only compatible and its default value is NULL. |
| SEGMENT_CREATED | varchar(3) | NO | Whether the materialized view was created using the SEGMENT CREATION DEFERRED clause.
NoteThis field is only compatible and its default value is NO. |
| EVALUATION_EDITION | varchar(128) | NO | The version name of the version object referenced in the parsed expression column
NoteThis field is only compatible and its default value is NULL. |
| UNUSABLE_BEFORE | varchar(128) | NO | The name of the oldest version, whose materialized view subquery results can be used for query rewrite. In versions earlier than the specified version, the stored results of materialized view data are considered unavailable. If no such version is specified, this value is NULL.
NoteThis field is only compatible and its default value is NULL. |
| UNUSABLE_BEGINNING | varchar(128) | NO | The name of the oldest version, in which the storage results of the materialized view subquery cannot be used for query rewrite. Data from this version and any of its descendants cannot be used for query rewrite. If no such version is specified, the value is NULL.
NoteThis field is only compatible and its default value is NULL. |
| DEFAULT_COLLATION | varchar(100) | NO | Default sorting rule of materialized view
NoteThis field is only compatible and 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 | Specifies the background refresh DOP for materialized views. The default value is 0. When the user does not explicitly set the refresh DOP (REFRESH_DOP is 0 by default), the background refresh DOP depends on the global system variable. mview_refresh_dop The value of . |
| DATA_SYNC_SCN | bigint(20) unsigned | NO | The data timestamp of the materialized view. |
| DATA_SYNC_DELAY | varchar(128) | NO | Data synchronization latency of the materialized view, in seconds. |
Sample query
Query all materialized view information 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