USER_MVIEWS|V4.3.5| docs|Distributed Database

USER_MVIEWS

Last Updated：2025-11-14 07:33:33 Updated

Note

This view is introduced since OceanBase Database V4.3.0.

Purpose

The USER_MVIEWS view displays the information about materialized views.

Columns

Column	Type	Nullable?	Description
OWNER	VARCHAR2(128)	YES	The owner of the materialized view.
MVIEW_NAME	VARCHAR2(128)	YES	The name of the materialized view.
CONTAINER_NAME	VARCHAR2(128)	YES	The name of the container table of the materialized view.
QUERY	CLOB	NO	The query that defines the materialized view.
QUERY_LEN	NUMBER	NO	The length of the query that defines the materialized view, in bytes.
UPDATABLE	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(1)	NO	Indicates whether rewrite is enabled. Valid values: `Y` `N`
REWRITE_CAPABILITY	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	DATE	YES	The date when the materialized view was last refreshed.
LAST_REFRESH_END_TIME	DATE	NO	The time when the last refresh of the materialized view ended.
STALENESS	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	DATE	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	NUMBER	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	NUMBER	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	NUMBER	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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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	VARCHAR2(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 SYS.USER_MVIEWS WHERE ROWNUM < = 1\G

The query result is as follows:

*************************** 1. row ***************************
                 OWNER: SYS
            MVIEW_NAME: MV_TEST_TBL1
        CONTAINER_NAME: MV_TEST_TBL1
                 QUERY: select "SYS"."TEST_TBL1"."COL1" AS "COL1","SYS"."TEST_TBL1"."COL2" AS "COL2","SYS"."TEST_TBL1"."COL3" AS "COL3" from "SYS"."TEST_TBL1"
             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: 21-FEB-25
 LAST_REFRESH_END_TIME: 21-FEB-25
             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: 1
1 row in set