USER_MVIEWS|V4.3.5| docs|Distributed Database

USER_MVIEWS

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

Note

This view is available starting with V4.3.0.

Purpose

The USER_MVIEWS view displays information about materialized views.

Columns

Column	Type	Nullable?	Description
OWNER	VARCHAR2(128)	YES	The name of the materialized view owner.
MVIEW_NAME	VARCHAR2(128)	YES	The name of the materialized view.
CONTAINER_NAME	VARCHAR2(128)	YES	The name of the materialized view container table.
QUERY	CLOB	NO	The query that defines the materialized view.
QUERY_LEN	NUMBER	NO	The length of the query, in bytes.
UPDATABLE	VARCHAR2(1)	NO	Indicates whether the materialized view is updatable. Y N Note This column is compatible only and defaults to N.
UPDATE_LOG	VARCHAR2(128)	NO	The name of the update log for updatable materialized views. Note This column is compatible only and defaults to NULL.
MASTER_ROLLBACK_SEG	VARCHAR2(128)	NO	The rollback segment for the master site or master materialized view site. Note This column is compatible only and defaults to NULL.
MASTER_LINK	VARCHAR2(128)	NO	The database link for the master site or master materialized view site. Note This column is compatible only and defaults to NULL.
REWRITE_ENABLED	VARCHAR2(1)	NO	Indicates whether rewrite is enabled. Y N
REWRITE_CAPABILITY	VARCHAR2(9)	NO	Indicates whether the materialized view meets the conditions for rewrite. If it does, the following rules apply: NONE: The materialized view cannot be used for rewrite because it is not allowed or prevented from being used for rewrite. TEXTMATCH: The materialized view's query has restrictions on query rewrite. GENERAL: The materialized view's definition query does not have restrictions on query rewrite. Note This column is compatible only and defaults to NULL.
REFRESH_MODE	VARCHAR2(6)	NO	The refresh mode for the materialized view. DEMAND: The materialized view is refreshed when the refresh process is called. NEVER: The materialized view is never refreshed.
REFRESH_METHOD	VARCHAR2(8)	NO	The default method for refreshing the materialized view (can be overridden via API). COMPLETE: A full refresh. FORCE: A fast refresh is attempted first. If a fast refresh is not possible, a full refresh is performed. FAST: A fast refresh. NEVER: The materialized view is never refreshed.
BUILD_MODE	VARCHAR2(9)	NO	How the materialized view is populated during creation. IMMEDIATE: The materialized view is populated immediately 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	VARCHAR2(18)	NO	Indicates whether the materialized view is suitable for fast (incremental) refresh. NO: The materialized view cannot be fast-refreshed and is therefore complex. DML: Only DML operations support fast refreshes. Note This column is compatible only and defaults to NULL.
LAST_REFRESH_TYPE	VARCHAR2(8)	NO	The method used for the last refresh. COMPLETE: A full refresh. FAST: A fast refresh. NA: The materialized view has not been refreshed yet (for example, for a deferred creation).
LAST_REFRESH_DATE	DATE	YES	The date when the materialized view was last refreshed.
LAST_REFRESH_END_TIME	DATE	NO	The end time of the last refresh of the materialized view.
STALENESS	VARCHAR2(19)	NO	The relationship between the content of the materialized view and the content of its master view. FRESH: The materialized view is a read-consistent view of the current state of its master object. IMPORT: The materialized view was 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 point in time. After a full refresh, the STALENESS value of this view will change to FRESH. NEEDS_COMPILE: Some objects that the materialized view depends on have changed. To verify this 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 changed. If the materialized view was previously FRESH and then became STALE, it was 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: Whether the materialized view is a read-consistent view of its master view at any point in time. UNUSABLE: The materialized view is not a read-consistent view of its master view at any point in time. Note This column is compatible only and defaults to NULL.
AFTER_FAST_REFRESH	VARCHAR2(19)	NO	The STALENESS value that will appear after a fast refresh is applied to this materialized view. The values are the same as those in the STALENESS column. If the value is NA, it indicates that this value is used when a fast refresh is not applicable to the materialized view. Note This column is compatible only and defaults to NULL.
UNKNOWN_PREBUILT	VARCHAR2(1)	NO	Indicates whether the materialized view is prebuilt. Y N Note This column is compatible only and defaults to N.
UNKNOWN_PLSQL_FUNC	VARCHAR2(1)	NO	Indicates whether the materialized view contains PL/SQL functions. Y N Note This column is compatible only and defaults to N.
UNKNOWN_EXTERNAL_TABLE	VARCHAR2(1)	NO	Indicates whether the materialized view contains external tables. Y N Note This column is compatible only and defaults to N.
UNKNOWN_CONSIDER_FRESH	VARCHAR2(1)	NO	Indicates whether the materialized view is considered fresh. Y N Note This column is for compatibility only. The default value is N.
UNKNOWN_IMPORT	VARCHAR2(1)	NO	Indicates whether the materialized view is imported. Y N Note This column is for compatibility only. The default value is N.
UNKNOWN_TRUSTED_FD	VARCHAR2(1)	NO	Indicates whether the materialized view is refreshed by using trusted constraints. Y N Note This column is for compatibility only. The default value is N.
COMPILE_STATE	VARCHAR2(19)	NO	The validity of the materialized view relative to the objects it depends on. VALID: The materialized view has been validated and has no errors. The objects it depends on have not changed since the last validation. NEEDS_COMPILE: Some objects that the materialized view depends on have changed. You must execute the ALTER MATERIALIZED VIEW...COMPILE statement to validate the materialized view. ERROR: The materialized view has been validated, but one or more errors exist. Note This column is for compatibility only. The default value is NULL.
USE_NO_INDEX	VARCHAR2(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 prevents the creation of the default index. Y N Note This column is for compatibility only. The default value is Y.
STALE_SINCE	DATE	NO	The start time when the materialized view becomes stale. Note This column is for compatibility only. The default value is NULL.
NUM_PCT_TABLES	NUMBER	NO	The number of PCT base tables. Note This column is for compatibility only. The default value is NULL.
NUM_FRESH_PCT_REGIONS	NUMBER	NO	The number of the latest PCT partition regions. Note This column is for compatibility only. The default value is NULL.
NUM_STALE_PCT_REGIONS	NUMBER	NO	The number of stale PCT partition regions. Note This column is for compatibility only. The default value is NULL.
SEGMENT_CREATED	VARCHAR2(3)	NO	Indicates whether the materialized view is created by using the SEGMENT CREATION DEFERRED clause. YES NO Note This column is for compatibility only. The default value is NO.
EVALUATION_EDITION	VARCHAR2(128)	NO	The name of the version object referenced in the expression column. Note This column is for compatibility only. The default value is NULL.
UNUSABLE_BEFORE	VARCHAR2(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, this value is NULL. Note This column is for compatibility only. The default value is NULL.
UNUSABLE_BEGINNING	VARCHAR2(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 all its descendants, the data cannot be used for query rewriting. If no such version is specified, this value is NULL. Note This column is for compatibility only. The default value is NULL.
DEFAULT_COLLATION	VARCHAR2(100)	NO	The default collation of the materialized view. Note This column is for compatibility only. The default value is NULL.
ON_QUERY_COMPUTATION	VARCHAR2(1)	NO	Indicates whether the materialized view is a real-time materialized view. Y N
REFRESH_DOP	NUMBER(38)	NO	The parallelism of the background refresh of the materialized view. The default value is 0. Note You can specify this value by using the `PARALLEL` attribute when you create a materialized view. 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`. This column is available starting with OceanBase Database V4.3.5 BP1.
DATA_SYNC_SCN	NUMBER(38)	NO	The data SCN of the materialized view. Note This column is available starting with OceanBase Database V4.3.5 BP2.
DATA_SYNC_DELAY	VARCHAR2(128)	NO	The data synchronization delay of the materialized view, in seconds. Note This column is available starting with OceanBase Database V4.3.5 BP2.

Sample query

obclient [SYS]> SELECT * FROM SYS.USER_MVIEWS WHERE ROWNUM < = 1\G

The query result is as follows:

*************************** 1. row ***************************
                 OWNER: SYS
            MVIEW_NAME: MV1_TBL3_TBL4
        CONTAINER_NAME: MV1_TBL3_TBL4
                 QUERY: select "SYS"."TBL3"."ID" AS "ID1","SYS"."TBL4"."ID" AS "ID2","SYS"."TBL3"."NAME" AS "NAME","SYS"."TBL4"."AGE" AS "AGE" from "SYS"."TBL3","SYS"."TBL4" where ("SYS"."TBL3"."ID" = "SYS"."TBL4"."ID")
             QUERY_LEN: 195
             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: 22-APR-25
 LAST_REFRESH_END_TIME: 22-APR-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: 0
         DATA_SYNC_SCN: 1745315408781183000
       DATA_SYNC_DELAY: +000061787 05:15:05.788800000
1 row in set