Databases Reference
In-Depth Information
Table 15-2.
MV Data Dictionary View Definitions
Data Dictionary View
Meaning
DBA/ALL/USER_MVIEWS
Information about MVs, such as owner, base query, last refresh time,
and so on
DBA/ALL/USER_MVIEW_REFRESH_TIMES
MV last refresh times, MV names, master table, and master owner
DBA/ALL/USER_REGISTERED_MVIEWS
All registered MVs; helps identify which MVs are using which MV logs
DBA/ALL/USER_MVIEW_LOGS
MV log information
DBA/ALL/USER_BASE_TABLE_MVIEWS
Base table names and last refresh dates for tables that have MV logs
DBA/ALL/USER_MVIEW_AGGREGATES
Aggregate functions that appear in
SELECT
clauses for MVs
DBA/ALL/USER_MVIEW_ANALYSIS
Information about MVs. Oracle recommends that you use
DBA/ALL/USER_MVIEWS
instead of these views.
DBA/ALL/USER_MVIEW_COMMENTS
Any comments associated with MVs
DBA/ALL/USER_MVIEW_DETAIL_PARTITION
Partition and freshness information
DBA/ALL/USER_MVIEW_DETAIL_
SUBPARTITION
Subpartition and freshness information
DBA/ALL/USER_MVIEW_DETAIL_RELATIONS
Local tables and MVs that an MV is dependent on
DBA/ALL/USER_MVIEW_JOINS
Joins between two columns in the
WHERE
clause of an MV definition
DBA/ALL/USER_MVIEW_KEYS
Columns or expressions in the
SELECT
clause of an MV definition
DBA/ALL/USER_TUNE_MVIEW
Result of executing the
DBMS_ADVISOR.TUNE_MVIEW
procedure
V$MVREFRESH
Information about MVs currently being refreshed
DBA/ALL/USER_REFRESH
Details about MV refresh groups
DBA_RGROUP
Information about MV refresh groups
DBA_RCHILD
Children in an MV refresh group
Creating Basic Materialized Views
This section covers how to create an MV. The two most common configurations used are as follows:
•
Creating complete refresh MVs that are refreshed on demand
•
Creating fast refresh MVs that are refreshed on demand
It's important to understand these basic configurations. They lay the foundation for everything else you do with
the MV feature. Therefore, this section starts with these basic configurations. Later, the section covers more advanced
configurations.
Creating a Complete Refreshable MV
This section explains how to set up an MV that is periodically completely refreshed, which is about the simplest
example possible. Complete refreshes are appropriate for MVs that have base tables in which significant portions of
the rows change from one refresh interval to the next. Complete refreshes are also required in situations in which
