DBMS_REFRESH 刷新组维护

--查看刷新组信息。
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');