DBA_MVIEWS

All materialized views in the database.

Name Datatype Length Mandatory Comments
OWNER VARCHAR2 (30) Yes Owner of the materialized view
MVIEW_NAME VARCHAR2 (30) Yes Name of the materialized view
CONTAINER_NAME VARCHAR2 (30) Yes Name of the materialized view container table
QUERY LONG (0)
The defining query that the materialized view instantiates
QUERY_LEN NUMBER

The number of bytes in the defining query (based on the server character set
UPDATABLE VARCHAR2 (1)
Indicates whether the materialized view can be updated
UPDATE_LOG VARCHAR2 (30)
Name of the table that logs changes to an updatable materialized view
MASTER_ROLLBACK_SEG VARCHAR2 (30)
Name of the rollback segment to use at the master site
MASTER_LINK VARCHAR2 (128)
Name of the database link to the master site
REWRITE_ENABLED VARCHAR2 (1)
Indicates whether rewrite is enabled for the materialized view
REWRITE_CAPABILITY VARCHAR2 (9)
Indicates the kind of rewrite that is enabled
REFRESH_MODE VARCHAR2 (6)
Indicates how and when the materialized view will be refreshed
REFRESH_METHOD VARCHAR2 (8)
The default refresh method for the materialized view (complete, fast, ...)
BUILD_MODE VARCHAR2 (9)
How and when to initially build (load) the materialized view container
FAST_REFRESHABLE VARCHAR2 (18)
Indicates the kinds of operations that can be fast refreshed for the MV
LAST_REFRESH_TYPE VARCHAR2 (8)
Indicates the kind of refresh that was last performed on the MV
LAST_REFRESH_DATE DATE

The date that the materialized view was last refreshed
STALENESS VARCHAR2 (19)
Indicates the staleness state of the materialized view (fresh, stale, ...)
AFTER_FAST_REFRESH VARCHAR2 (19)
Indicates the staleness state the MV will have after a fast refresh is done
UNKNOWN_PREBUILT VARCHAR2 (1)
Indicates if the materialized view is prebuilt
UNKNOWN_PLSQL_FUNC VARCHAR2 (1)
Indicates if the materialized view contains PL/SQL function
UNKNOWN_EXTERNAL_TABLE VARCHAR2 (1)
Indicates if the materialized view contains external tables
UNKNOWN_CONSIDER_FRESH VARCHAR2 (1)
Indicates if the materialized view is considered fresh
UNKNOWN_IMPORT VARCHAR2 (1)
Indicates if the materialized view is imported
COMPILE_STATE VARCHAR2 (19)
Indicates the validity of the MV meta-data
USE_NO_INDEX VARCHAR2 (1)
Indicates whether the MV uses no index

Usage:


  cursor cur_mview(c_name in dba_objects.object_name%type
                 , c_owner in dba_objects.owner%type) is
  SELECT CONTAINER_NAME
  ,      UPDATABLE
  ,      REWRITE_ENABLED
  ,      REWRITE_CAPABILITY
  ,      REFRESH_MODE
  ,      REFRESH_METHOD
  ,      BUILD_MODE
  ,      FAST_REFRESHABLE
  FROM SYS.DBA_MVIEWS
  where OWNER = c_owner
  and   MVIEW_NAME = c_name;

cursor cur_mview(c_name  in dba_objects.object_name%type
               , c_owner in dba_objects.owner%type)
is select query
   from dba_mviews
   where mview_name = c_name
   and   owner = c_owner;

  cursor cur_dtree(c_name in dba_objects.object_name%type
                 , c_owner in dba_objects.owner%type) is
  select LPAD(' ',5*(LEVEL-1)) || a.master  tree_entry
  from SYS.SNAP_REFTIME$ a
  start with a.vname = c_name
  connect by prior a.master = a.vname;