Database Reference
In-Depth Information
To display the parameters associated with a materialized view, whether it's fresh, and how and when it was last
refreshed, you can query the
user_mviews
view (
all
,
dba
, and, in a 12.1 multitenant environment,
cdb
variants of this
view are also available):
SQL> SELECT refresh_method, refresh_mode, staleness, last_refresh_type, last_refresh_date
2 FROM user_mviews
3 WHERE mview_name = 'SALES_MV';
REFRESH_METHOD REFRESH_MODE STALENESS LAST_REFRESH_TYPE LAST_REFRESH_DATE
-------------- ------------ --------- ----------------- -----------------
FORCE DEMAND FRESH COMPLETE 2013-12-10 15:51
When you choose to manually refresh a materialized view, you call one of the following procedures in the
dbms_mview
package:
refresh
: This procedure refreshes the materialized views specified as a comma-separated
list through the
list
parameter. For example, the following call refreshes the
sales_mv
and
cal_month_sales_mv
materialized views that are owned by the
sh
user:
•
dbms_mview.refresh(list => 'sh.sales_mv,sh.cal_month_sales_mv')
refresh_all_mviews
: This procedure refreshes all materialized views stored in the database
except those that are marked to never be refreshed. The output parameter
number_of_
failures
returns the number of failures that occurred during processing:
•
dbms_mview.refresh_all_mviews(number_of_failures => :r)
refresh_dependent
: This procedure refreshes the materialized views that depend on the
base tables that are specified as a comma-separated list through the parameter
list
. The
output parameter
number_of_failures
returns the number of failures that occurred during
processing. For example, the following call refreshes all materialized views, depending on the
sales
table owned by the
sh
user:
•
dbms_mview.refresh_dependent(number_of_failures => :r, list => 'sh.sales')
All these procedures also support the parameters
method
and
atomic_refresh
. The former specifies how the
refresh is done (
'c'
for complete,
'f'
for fast,
'p'
for PCT refresh, and
'?'
for force), and the latter specifies whether
the refresh is performed in a single transaction. If the
atomic_refresh
parameter is set to
FALSE
(the default value is
TRUE
), no single transaction is used. As a result, for complete refreshes, the materialized views are truncated instead
of being deleted. On the one hand, the refresh is faster. On the other hand, if another session queries the materialized
view while a refresh is running, the query might return a wrong result (no rows selected).
In addition, from version 12.1 onward, a new parameter called
out_of_place
is available. If the
out_of_place
parameter is set to
FALSE
(the default value), refreshes are directly performed on the container table associated to the
materialized view. Such refreshes might lead to some issues for concurrent queries accessing the materialized view.
If
out_of_place
is set to
TRUE
, refreshes are performed with the help of another table. What happens is that a new
container table is created, the up-to-date data is inserted into it through a direct-path insert, the new container table is
switched with the old container table, and finally the old container table is dropped. This method, called
out-of-place
refresh
, makes sure that the impact on concurrent queries accessing the materialized is minimized. The drawback is
that during the refresh, twice as much space is needed.
