本文介绍了如何使用DBMS_MVIEW.REFRESH_ALL_MVIEWS过程来刷新数据仓库中所有物化视图，并通过具体案例展示了操作步骤。包括触发条件、语法参数解释、常见错误及解决方法，以及刷新前后物化视图的状态对比。
REFRESH_ALL_MVIEWS Procedure
此过程会刷新符合如下特征的所有物化视图
This procedure refreshes all materialized views that have the following properties:
物化视图不会刷新因为它依赖于master table or master materiazlied view的最新变化
The materialized view has not been refreshed since the most recent change to a master table or master materialized view on which it depends.
物化视图及其依赖的master table or master materialized view必须全是本地
The materialized view and all of the master tables or master materialized views on which it depends are local.
物化视图必须位于dba_mviews
The materialized view is in the view DBA_MVIEWS.
此过程用于数据仓库
This procedure is intended for use with data warehouses.
Syntax
DBMS_MVIEW.REFRESH_ALL_MVIEWS (
number_of_failures OUT BINARY_INTEGER,
method IN VARCHAR2 := NULL,--诸元素语义同上
rollback_seg IN VARCHAR2 := NULL,
refresh_after_errors IN BOOLEAN := false,
atomic_refresh IN BOOLEAN := true);
--物化视图可以truncate
SQL> truncate table mv_t;
Table truncated
SQL> select * from mv_t;
SQL> set serverout on
SQL> declare
2 out_err number;
3 begin
4 dbms_mview.refresh_all_mviews(number_of_failures => out_err,method => 'f',refresh_after_errors => true,atomic_refresh => false);
5 dbms_output.put_line(out_err);
6 end;
7 /
5
PL/SQL procedure successfully completed --刷新所有物化视图有5个错误
SQL> select * from mv_t;
A
---------------------------------------
--系统共有5个物化视图
SQL> select * from dba_mviews;
OWNER MVIEW_NAME CONTAINER_NAME QUERY QUERY_LEN UPDATABLE UPDATE_LOG MASTER_ROLLBACK_SEG MASTER_LINK REWRITE_ENABLED REWRITE_CAPABILITY REFRESH_MODE REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE LAST_REFRESH_TYPE LAST_REFRESH_DATE STALENESS AFTER_FAST_REFRESH UNKNOWN_PREBUILT UNKNOWN_PLSQL_FUNC UNKNOWN_EXTERNAL_TABLE UNKNOWN_CONSIDER_FRESH UNKNOWN_IMPORT UNKNOWN_TRUSTED_FD COMPILE_STATE USE_NO_INDEX STALE_SINCE NUM_PCT_TABLES NUM_FRESH_PCT_REGIONS NUM_STALE_PCT_REGIONS
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------------------------------- --------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------------ ------------ -------------- ---------- ------------------ ----------------- ----------------- ------------------- ------------------- ---------------- ------------------ ---------------------- ---------------------- -------------- ------------------ ------------------- ------------ ----------- -------------- --------------------- ---------------------
SCOTT MV_9 MV_9 select a from t_mv 19 N N GENERAL COMMIT FORCE IMMEDIATE DML COMPLETE 2013/2/20 10:04:0 UNUSABLE NA N N N N N N VALID N 0
SCOTT MV_ROWID MV_ROWID select a from t_rowid connect by level<=3 42 N N TEXTMATCH DEMAND FORCE IMMEDIATE NO COMPLETE 2013/2/20 20:25:3 FRESH NA N N N N N N VALID N 0
SCOTT MV_1 MV_1 select a from t_mv 19 N N GENERAL DEMAND FORCE IMMEDIATE NO COMPLETE 2013/2/21 20:24:5 UNUSABLE NA N N N N N N VALID N 0
SCOTT MV_TOP MV_TOP select mv_1.a from mv_1,t_mv where mv_1.a=t_mv.a 50 N N GENERAL DEMAND FORCE IMMEDIATE NO COMPLETE 2013/2/21 20:25:3 UNUSABLE NA N N N N N N VALID N 0
SCOTT MV_T MV_T select a from t_mv 19 N N GENERAL COMMIT FAST IMMEDIATE DML FAST 2013/2/22 23:59:2 UNUSABLE NA N N N N N N VALID N 0
A
---------------------------------------
SQL> drop materialized view mv_t;
Materialized view dropped
--删除完所有物化视图再执行过程结果为0
SQL>
SQL> declare
2 out_err number;
3 begin
4 dbms_mview.refresh_all_mviews(number_of_failures => out_err,method => 'f',refresh_after_errors => true,atomic_refresh => false);
5 dbms_output.put_line(out_err);
6 end;
7 /
0
PL/SQL procedure successfully completed
--mv filter column
SQL> desc dba_mview_log_filter_cols;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------------------------------------
OWNER VARCHAR2(30) Y Owner of the master table being logged
NAME VARCHAR2(30) Y Name of the master table being logged
COLUMN_NAME VARCHAR2(30) Y Filter column being logged
SQL> desc dba_mview_aggregates;
Name Type Nullable Default Comments
------------------ ------------ -------- ------- ----------------------------------------------------------------------------------------------------
OWNER VARCHAR2(30) Owner of the materialized view
MVIEW_NAME VARCHAR2(30) Name of the materialized view
POSITION_IN_SELECT NUMBER Position of this aggregated measure with the SELECT list
CONTAINER_COLUMN VARCHAR2(30) Name of this column in the container table
AGG_FUNCTION VARCHAR2(8) Y Name of the aggregation function, one of the following:
COUNT, SUM, MIN, MAX, AVG, VARIANCE, STDDEV
DISTINCTFLAG VARCHAR2(1) Y Set to Y is this is a DISTINCT aggregation
MEASURE LONG Y The SQL text of the measure, excluding the aggregation function
SQL>
SQL> desc dba_mview_detail_partition;
Name Type Nullable Default Comments
------------------------- ------------ -------- ------- ----------------------------------------
OWNER VARCHAR2(30) Owner of the materialized view
MVIEW_NAME VARCHAR2(30) Name of the materialized view
DETAILOBJ_OWNER VARCHAR2(30)
DETAILOBJ_NAME VARCHAR2(30) Name of the detail object
DETAIL_PARTITION_NAME VARCHAR2(30) Y Name of the detail object partition
DETAIL_PARTITION_POSITION NUMBER Y Position of the detail object partition
FRESHNESS CHAR(5) Y Freshness of the detail object partition
SQL> desc dba_mview_joins;
Name Type Nullable Default Comments
------------------- ------------ -------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
OWNER VARCHAR2(30) Owner of the materialized view
MVIEW_NAME VARCHAR2(30) Name of the materialized view
DETAILOBJ1_OWNER VARCHAR2(30) Owner of the 1st detail object
DETAILOBJ1_RELATION VARCHAR2(30) Name of the 1st detail object
DETAILOBJ1_COLUMN VARCHAR2(30) Name of the 1st detail object column
OPERATOR CHAR(1) Y Name of the join operator. Currently nly = is defined
OPERATOR_TYPE VARCHAR2(1) Y Indicates inner or outer join. I = inner join, L = DETAILOBJ1 table
is the left side of an outer join, R = DETAILOBJ2 table is the right
side of an outer join
DETAILOBJ2_OWNER VARCHAR2(30) Owner of the 2nd detail object
DETAILOBJ2_RELATION VARCHAR2(30) Name of the 2nd detail object
DETAILOBJ2_COLUMN VARCHAR2(30) Name of the 2nd detail object column
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-755172/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-755172/
扫一扫
2630
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
