刷新组的维护

查看刷新组相关信息

--查看刷新组信息。
SQL> select rowner,rname,job,next_date,interval from dba_refresh;

ROWNER               RNAME                 JOB NEXT_DATE            INTERVAL
-------------------- -------------------- ---- -------------------- --------------------
MVADMIN              REP_REFRESH             3 17-1月 -12           SYSDATE + 1/24

--查看刷新组子信息，包含具体的物化视图，以下 TEST_REP 就是我们创建的MV 。
SQL> select owner,name,rowner,rname,job from dba_refresh_children;

OWNER                NAME                 ROWNER               RNAME                 JOB
-------------------- -------------------- -------------------- -------------------- ----
TEST                 TEST_REP             MVADMIN              REP_REFRESH             3

--根据job号，可以查看该job的详细内容。以下可知，oracle调用dbms_refresh.refresh对整个刷新
--组（REP_REFRESH）进行刷新。
SQL> col priv_user for a20
SQL> col schema_user for a20
SQL> col interval for a20
SQL> col what for a20
SQL> select job,priv_user,schema_user,interval,what from dba_jobs where job=3;

 JOB PRIV_USER            SCHEMA_USER          INTERVAL             WHAT
---- -------------------- -------------------- -------------------- --------------------
   3 MVADMIN              MVADMIN              SYSDATE + 1/24       dbms_refresh.refresh
                                                                    ('"MVADMIN"."REP_REF
                                                                    RESH"');

与刷新组相关的操作

--新建一个刷新组
begin 
DBMS_REFRESH.MAKE ( 
	name => 'mvadmin.rep_refresh', 
	list => '', 
	next_date => SYSDATE, 
	interval => 'SYSDATE + 1/24', 
	implicit_destroy => FALSE, 
	rollback_seg => '', 
	push_deferred_rpc => TRUE, 
	refresh_after_errors => FALSE); 
end;
/ 

--添加MV到一个刷新组
begin 
DBMS_REFRESH.ADD(
    name => 'rep_refresh',
    list => 'test.testmv'
    );
end;
/

--更改刷新组的刷新间隔
begin 
DBMS_REFRESH.CHANGE(
    name => 'rep_refresh',
    next_date => sysdate,
    interval => sysdate+(1/(24*60))
    );
end;
/
																	
--手动刷新一个刷新组
SQL> exec DBMS_REFRESH.REFRESH('rep_refresh');																	
																	
--从刷新组中移除一个物化视图
begin
DBMS_REFRESH.SUBTRACT (
   name => 'mvadmin.rep_refresh',
   list => 'test.testmv'
   );
end;
/																	

--移除所有的物化视图并删除刷新组
exec DBMS_REFRESH.DESTROY('rep_refresh');	

参考：
http://docs.oracle.com/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#REPMA018