DBA_MVIEWS

2025-11-14 07:33:33  Updated

Note

This view is introduced since OceanBase Database V4.3.0.

Purpose

The DBA_MVIEWS view displays the information about all materialized views.

Columns

Column Type Nullable? Description
OWNER varchar(128) NO 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 that defines the materialized view, in bytes.
UPDATABLE varchar(1) NO Indicates whether the materialized view can be updated. Valid values:
  • Y
  • N

Note

This column is used only for compatibility and its value is N by default.
UPDATE_LOG varchar(128) NO The name of the update log file, if the materialized view can be updated.

Note

This column is used only for compatibility and its value is NULL by default.
MASTER_ROLLBACK_SEG varchar(128) NO The rollback segment of the master site or master materialized view site.

Note

This column is used only for compatibility and its value is NULL by default.
MASTER_LINK varchar(128) NO The database link of the master site or master materialized view site.

Note

This column is used only for compatibility and its value is NULL by default.
REWRITE_ENABLED varchar(1) NO Indicates whether rewrite is enabled. Valid values:
  • Y
  • N
REWRITE_CAPABILITY varchar(9) NO Indicates whether the materialized view meets the rewrite conditions. Valid values:
  • NONE: The materialized view cannot be used for query rewrites because rewrites are not allowed or are blocked.
  • TEXTMATCH: The query that defines the materialized view contains the restrictions on using query rewrites.
  • GENERAL: The query that defines the materialized view does not contain the restrictions on using query rewrites.

Note

This column is used only for compatibility and its value is NULL by default.
REFRESH_MODE varchar(6) NO The mode for refreshing the materialized view. Valid values:
  • DEMAND: The materialized view is refreshed by calling the refresh procedure.
  • NEVER: The materialized view is never refreshed.
REFRESH_METHOD varchar(8) NO The default method for refreshing the materialized view, which can be overridden by using an API. Valid values:
  • COMPLETE: The materialized view is completely refreshed.
  • FORCE: A fast refresh is performed if possible. Otherwise, a complete refresh is performed.
  • FAST: The materialized view is fast refreshed.
  • NEVER: The materialized view is never refreshed.
BUILD_MODE varchar(9) NO The mode for populating the materialized view during creation. Valid values:
  • IMMEDIATE: The materialized view is immediately populated during creation.
  • DEFERRED: The materialized view is not populated during creation.
  • PREBUILT: The materialized view is populated based on existing tables during creation.
FAST_REFRESHABLE varchar(18) NO Indicates whether the materialized view is eligible for incremental (fast) refreshes. Valid values:
  • NO: The materialized view cannot be fast refreshed and therefore is complex.
  • DML: Fast refreshes are supported only for DML operations.

Note

This column is used only for compatibility and its value is NULL by default.
LAST_REFRESH_TYPE varchar(8) NO The method used for the last refresh. Valid values:
  • COMPLETE: The materialized view is completely refreshed.
  • FAST: The materialized view is fast refreshed.
  • NA: The materialized view has not been refreshed. For example, the materialized view was created in DEFERRED mode.
LAST_REFRESH_DATE datetime YES The date when the materialized view was last refreshed.
LAST_REFRESH_END_TIME datetime NO The time when the last refresh of the materialized view ended.
STALENESS varchar(19) NO The relationship between the content of the materialized view and that of the masters of the materialized view. Valid values:
  • FRESH: The materialized view is a read-consistent view of its masters in the current state.
  • IMPORT: The materialized view is imported from another database. In this case, the value of the UNKNOWN_IMPORT column is Y. Therefore, it is unknown whether the materialized view is a read-consistent view of its masters at any point in time. After a complete refresh, the STALENESS value of the view changes to FRESH.
  • NEEDS_COMPILE: Some objects on which the materialized view depends have changed. In this case, you must execute the ALTER MATERIALIZED VIEW...COMPILE statement to verify this materialized view and calculate the staleness of its data.
  • STALE: The materialized view is stale because one or more of its masters have changed. If the STALENESS value of the materialized view changes from FRESH to STALE, the materialized view is a read-consistent view of its masters in an earlier state.
  • UNDEFINED: The materialized view has remote masters. The concept of staleness is not defined for this type of materialized views.
  • UNKNOWN: It is unknown whether the materialized view is a read-consistent view of its masters at any point in time.
  • UNUSABLE: The materialized view is not a read-consistent view of its masters at any point in time.

Note

This column is used only for compatibility and its value is NULL by default.
AFTER_FAST_REFRESH varchar(19) NO The STALENESS value when the materialized view is fast refreshed, which is the same as the value of the STALENESS column. The value NA indicates that fast refresh is not supported for the materialized view.

Note

This column is used only for compatibility and its value is NULL by default.
UNKNOWN_PREBUILT varchar(1) NO Indicates whether the materialized view was prebuilt. Valid values:
  • Y
  • N

Note

This column is used only for compatibility and its value is N by default.
UNKNOWN_PLSQL_FUNC varchar(1) NO Indicates whether the materialized view contains PL/SQL functions. Valid values:
  • Y
  • N

Note

This column is used only for compatibility and its value is N by default.
UNKNOWN_EXTERNAL_TABLE varchar(1) NO Indicates whether the materialized view contains external tables. Valid values:
  • Y
  • N

Note

This column is used only for compatibility and its value is N by default.
UNKNOWN_CONSIDER_FRESH varchar(1) NO Indicates whether the materialized view is considered fresh. Valid values:
  • Y
  • N

Note

This column is used only for compatibility and its value is N by default.
UNKNOWN_IMPORT varchar(1) NO Indicates whether the materialized view was imported from another database. Valid values:
  • Y
  • N

Note

This column is used only for compatibility and its value is N by default.
UNKNOWN_TRUSTED_FD varchar(1) NO Indicates whether trusted constraints are used to refresh the materialized view. Valid values:
  • Y
  • N

Note

This column is used only for compatibility and its value is N by default.
COMPILE_STATE varchar(19) NO The validity of the materialized view with respect to the objects on which it depends. Valid values:
  • VALID: The materialized view has been verified and no errors were found. In addition, the objects on which the materialized view depends have not changed since the last verification.
  • NEEDS_COMPILE: Some objects on which the materialized view depends have changed. In this case, you must execute the ALTER MATERIALIZED VIEW...COMPILE statement to verify this materialized view.
  • ERROR: The materialized view has been verified and one or more errors were found.

Note

This column is used only for compatibility and its value is NULL by default.
USE_NO_INDEX varchar(1) NO Indicates whether the materialized view was created by using the USING NO INDEX clause (Y) or the default index (N). You cannot create a default index when you use the USING NO INDEX clause.
  • Y
  • N

Note

This column is used only for compatibility and its value is Y by default.
STALE_SINCE datetime NO The time since when the materialized view became stale.

Note

This column is used only for compatibility and its value is NULL by default.
NUM_PCT_TABLES bigint(0) NO The number of PCT base tables.

Note

This column is used only for compatibility and its value is NULL by default.
NUM_FRESH_PCT_REGIONS bigint(0) NO The number of fresh PCT partition regions.

Note

This column is used only for compatibility and its value is NULL by default.
NUM_STALE_PCT_REGIONS bigint(0) NO The number of stale PCT partition regions.

Note

This column is used only for compatibility and its value is NULL by default.
SEGMENT_CREATED varchar(3) NO Indicates whether the materialized view was created by using the SEGMENT CREATION DEFERRED clause. Valid values:
  • YES
  • NO

Note

This column is used only for compatibility and its value is NO by default.
EVALUATION_EDITION varchar(128) NO The edition in which editioned objects referenced in an expression column are resolved.

Note

This column is used only for compatibility and its value is NULL by default.
UNUSABLE_BEFORE varchar(128) NO The earliest edition in which the stored results of subqueries on the materialized view can be used for query rewrites. In an edition earlier than the specified edition, the stored query results of the materialized view are considered as unavailable. If no such edition is specified, the value is NULL.

Note

This column is used only for compatibility and its value is NULL by default.
UNUSABLE_BEGINNING varchar(128) NO The earliest edition in which the stored results of subqueries on the materialized view cannot be used for query rewrites. In the specified edition and later editions, the data cannot be used for query rewrites. If no such edition is specified, the value is NULL.

Note

This column is used only for compatibility and its value is NULL by default.
DEFAULT_COLLATION varchar(100) NO The default collation for the materialized view.

Note

This column is used only for compatibility and its value is NULL by default.
ON_QUERY_COMPUTATION varchar(1) NO Indicates whether the materialized view is a real-time view. Valid values:
  • Y
  • N
REFRESH_DOP bigint(20) NO Indicates the background refresh parallelism for materialized views. The default value is 0.

Note

  • When creating a materialized view, you can specify this value using the PARALLEL attribute. It can also be modified later using the ALTER MATERIALIZED VIEW syntax. If the user does not explicitly set PARALLEL (and REFRESH_DOP remains at its default value of 0), the background refresh parallelism will depend on the value of the global system variable mview_refresh_dop.
  • This column was introduced starting from V4.3.5 BP1 in OceanBase Database V4.3.5.

Sample query

SELECT * FROM oceanbase.DBA_MVIEWS LIMIT 1\G

The query result is as follows:

*************************** 1. row ***************************
                 OWNER: db_test
            MVIEW_NAME: mv1_t1
        CONTAINER_NAME: mv1_t1
                 QUERY: select `db_test`.`t1`.`c1` AS `c1`,`db_test`.`t1`.`c2` AS `c2` from `db_test`.`t1`
             QUERY_LEN: 82
             UPDATABLE: N
            UPDATE_LOG: NULL
   MASTER_ROLLBACK_SEG: NULL
           MASTER_LINK: NULL
       REWRITE_ENABLED: N
    REWRITE_CAPABILITY: NULL
          REFRESH_MODE: DEMAND
        REFRESH_METHOD: COMPLETE
            BUILD_MODE: IMMEDIATE
      FAST_REFRESHABLE: NULL
     LAST_REFRESH_TYPE: COMPLETE
     LAST_REFRESH_DATE: 2025-02-21 14:02:57
 LAST_REFRESH_END_TIME: 2025-02-21 14:02:57
             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: 8
1 row in set
Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartnerTrust Center

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite Paper

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us