物化视图批量刷新存储过程及JOB指定

第一步：在源库建立物化视图日志

CREATE MATERIALIZED VIEW LOG ON table_name;

 

第二步：在目标库建立物化视图

自带刷新功能的语法：create MATERIALIZED VIEW REV_01_TVM_COIN_BOX REFRESH FORCE START WITH SYSDATE NEXT TRUNC(SYSDATE+1)+2/24 AS SELECT * FROM REV_01_TVM_COIN_BOX@FS3_LCS;

不带刷新的语法：create MATERIALIZED VIEW XXX AS SELECT * FROM REV_01_TVM_COIN_BOX@FS3_LCS;

第三步：建立刷新物化视图的存储过程

create or replace procedure pro_refresh_maview is
begin
declare CURSOR c_number is
select user||'.'||mview_name mview_name from user_mviews;
my_re c_number%rowtype;
begin
open c_number;
loop
exit when c_number%notfound;
fetch c_number into my_re;
DBMS_MVIEW.REFRESH(my_re.mview_name,'F');
end loop;
close c_number;
end;
end pro_refresh_maview;

第四步：指定JOB，定时调用存过刷新物化视图

declare job_id int;
begin
select max(job) into job_id from user_jobs where WHAT='pro_refresh_maview;';
if job_id>0 then
dbms_job.remove(job_id);
commit;
end if;
end;
/

DECLARE
JOB NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
JOB => JOB,
WHAT => 'pro_refresh_maview;',
NEXT_DATE =>sysdate,
INTERVAL => 'sysdate+120/1440'
);
COMMIT;
END;
/