oceanbase.DBA_MVIEWS|V4.3.5| docs|Distributed Database

oceanbase.DBA_MVIEWS

Last Updated：2026-02-05 05:53:37 Updated

Note

This view is available starting with V4.3.0.

Purpose

The DBA_MVIEWS view displays information about all materialized views.

Columns

Column	Type	Nullable?	Description
OWNER	varchar(128)	NO	The name of the owner of the materialized view.
MVIEW_NAME	varchar(128)	NO	The name of the materialized view.
CONTAINER_NAME	varchar(128)	NO	The name of the container table of the materialized view.
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. Valid values: Y and N. Y N Note This column is retained for compatibility. Its value is N by default.
UPDATE_LOG	varchar(128)	NO	The name of the update log file for updatable materialized views. Note This column is retained for compatibility. Its value is NULL by default.
MASTER_ROLLBACK_SEG	varchar(128)	NO	The rollback segment of the master site or the master materialized view site. Note This column is retained for compatibility. Its value is NULL by default.
MASTER_LINK	varchar(128)	NO	The database link of the master site or the master materialized view site. Note This column is retained for compatibility. Its value is NULL by default.
REWRITE_ENABLED	varchar(1)	NO	Indicates whether query rewrite is enabled. Valid values: Y and N. Y N
REWRITE_CAPABILITY	varchar(9)	NO	Indicates whether the materialized view meets the requirements for query rewrite. Valid values: NONE, TEXTMATCH, and GENERAL. NONE: The materialized view cannot be used for query rewrite because query rewrite is not allowed or is blocked. TEXTMATCH: The query that defines the materialized view has restrictions on query rewrite. GENERAL: The query that defines the materialized view has no restrictions on query rewrite. Note This column is retained for compatibility. Its value is NULL by default.
REFRESH_MODE	varchar(6)	NO	The refresh mode of the materialized view. Valid values: DEMAND and NEVER. DEMAND: The materialized view is refreshed when the refresh process is called. NEVER: The materialized view is never refreshed.
REFRESH_METHOD	varchar(8)	NO	The default method for refreshing the materialized view. This method can be overridden by using an API. Valid values: COMPLETE, FORCE, FAST, and NEVER. COMPLETE: The materialized view is completely refreshed. FORCE: The materialized view is incrementally refreshed if possible. If incremental refresh is not possible, the materialized view is completely refreshed. FAST: The materialized view is incrementally refreshed. NEVER: The materialized view is never refreshed.
BUILD_MODE	varchar(9)	NO	The method for populating the materialized view during creation. Valid values: IMMEDIATE, DEFERRED, and PREBUILT. IMMEDIATE: The materialized view is populated during creation. DEFERRED: The materialized view is not populated during creation. PREBUILT: The materialized view is populated with an existing table during creation.
FAST_REFRESHABLE	varchar(18)	NO	Indicates whether the materialized view is suitable for incremental (fast) refresh. Valid values: NO and DML. NO: The materialized view cannot be fast-refreshed. Therefore, it is complex. DML: Only DML operations support fast refresh. Note This column is retained for compatibility. Its value is NULL by default.
LAST_REFRESH_TYPE	varchar(8)	NO	The method used for the last refresh. Valid values: COMPLETE, FAST, and NA. COMPLETE: The materialized view is completely refreshed. FAST: The materialized view is incrementally refreshed. NA: The materialized view has not been refreshed yet. For example, it is created with the DEFERRED option.
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 last 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. Valid values: FRESH, IMPORT, NEEDS_COMPILE, STALE, UNDEFINED, UNKNOWN, and UNUSABLE. FRESH: The materialized view is a read-consistent view of the current state of its master object. IMPORT: The materialized view is imported from another database. The value of the UNKNOWN_IMPORT column is Y. Therefore, it is unknown whether the materialized view is a read-consistent view of its master view at any given point in time. After a complete refresh, the STALENESS value of this view becomes FRESH. NEEDS_COMPILE: Some objects that the materialized view depends on have been modified. To verify the materialized view and calculate the staleness, use the ALTER MATERIALIZED VIEW...COMPILE statement. STALE: The materialized view is outdated because one or more of its master objects have been modified. If the materialized view was FRESH before it became STALE, it is a read-consistent view of the previous state of its master objects. UNDEFINED: The materialized view has a remote master. The concept of staleness is not defined for such materialized views. UNKNOWN: It is unknown whether the materialized view is a read-consistent view of its master view at any given point in time. UNUSABLE: The materialized view is not a read-consistent view of its master view at any given point in time. Note This column is retained for compatibility. Its value is NULL by default.
AFTER_FAST_REFRESH	varchar(19)	NO	The STALENESS value that appears after a fast refresh is applied to this materialized view. Its value is the same as that of the STALENESS column. If the value is NA, it indicates that the fast refresh is not applicable to this materialized view. Note This column is retained for compatibility. Its value is NULL by default.
UNKNOWN_PREBUILT	varchar(1)	NO	Indicates whether the materialized view is prebuilt. Valid values: Y and N. Y N Note This column is retained for compatibility. Its value is N by default.
UNKNOWN_PLSQL_FUNC	varchar(1)	NO	Indicates whether the materialized view contains PL/SQL functions. Valid values: Y and N. Y N Note This column is retained for compatibility. Its value is N by default.
UNKNOWN_EXTERNAL_TABLE	varchar(1)	NO	Indicates whether the materialized view contains external tables. Valid values: Y and N. Y N Note This column is retained for compatibility. Its value is N by default.
UNKNOWN_CONSIDER_FRESH	varchar(1)	NO	Indicates whether a materialized view is considered fresh. Y N Note This column is compatible only and defaults to N.
UNKNOWN_IMPORT	varchar(1)	NO	Indicates whether a materialized view is imported. Y N Note This column is compatible only and defaults to N.
UNKNOWN_TRUSTED_FD	varchar(1)	NO	Indicates whether a materialized view is refreshed by using trusted constraints. Y N Note This column is compatible only and defaults to N.
COMPILE_STATE	varchar(19)	NO	Indicates the validity of a materialized view relative to the objects on which it depends. VALID: The materialized view has been validated, has no errors, and the objects on which it depends have not changed since the last validation. NEEDS_COMPILE: Some objects on which the materialized view depends have changed. Use the ALTER MATERIALIZED VIEW...COMPILE statement to validate this materialized view. ERROR: The materialized view has been validated, but one or more errors exist. Note This column is compatible only and defaults to NULL.
USE_NO_INDEX	varchar(1)	NO	Indicates whether a 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 a default index. Y N Note This column is compatible only and defaults to Y.
STALE_SINCE	datetime	NO	The start time when a materialized view becomes stale Note This column is compatible only and defaults to NULL.
NUM_PCT_TABLES	bigint(0)	NO	The number of PCT base tables Note This column is compatible only and defaults to NULL.
NUM_FRESH_PCT_REGIONS	bigint(0)	NO	The number of fresh PCT partition regions Note This column is compatible only and defaults to NULL.
NUM_STALE_PCT_REGIONS	bigint(0)	NO	The number of stale PCT partition regions Note This column is compatible only and defaults to NULL.
SEGMENT_CREATED	varchar(3)	NO	Indicates whether a materialized view is created by using the SEGMENT CREATION DEFERRED clause. YES NO Note This column is compatible only and defaults to NO.
EVALUATION_EDITION	varchar(128)	NO	The name of the version of the object referenced in the expression column Note This column is compatible only and defaults to NULL.
UNUSABLE_BEFORE	varchar(128)	NO	The name of the oldest version in which the stored results of the subquery of a materialized view can be used for query rewriting. The stored results of a materialized view are considered unavailable in versions earlier than the specified version. If no such version is specified, this value is NULL. Note This column is compatible only and defaults to NULL.
UNUSABLE_BEGINNING	varchar(128)	NO	The name of the oldest version in which the stored results of the subquery of a materialized view 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, this value is NULL. Note This column is compatible only and defaults to NULL.
DEFAULT_COLLATION	varchar(100)	NO	The default collation of a materialized view Note This column is compatible only and defaults to NULL.
ON_QUERY_COMPUTATION	varchar(1)	NO	Indicates whether a materialized view is an online materialized view Y N
REFRESH_DOP	bigint(20)	NO	The parallelism of the background refresh of a materialized view. The default value is 0. Note When you create a materialized view, you can specify this value by using the `PARALLEL` attribute. You can also modify this value by using the `ALTER MATERIALIZED VIEW` statement. If you do not explicitly set `PARALLEL` (the default value of `REFRESH_DOP` is 0), the background refresh parallelism depends on the value of the global system variable `mview_refresh_dop`. For OceanBase Database V4.3.5, this column is available starting from V4.3.5 BP1.
DATA_SYNC_SCN	bigint(20) unsigned	NO	The data point of a materialized view. Note For OceanBase Database V4.3.5, this column is available starting from V4.3.5 BP2.
DATA_SYNC_DELAY	varchar(128)	NO	The data synchronization delay of a materialized view, in seconds. Note For OceanBase Database V4.3.5, this column is available starting from V4.3.5 BP2.

Sample query

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