oracle 创建每 5 分钟刷新一次的物化视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10061597/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating materialized view that refreshes every 5 min
提问by kinkajou
I created a materialized view that refreshed every 5 min but when I do insert and perform select on materialized view I get same old data? Do I need to refresh manually?
我创建了一个每 5 分钟刷新一次的物化视图，但是当我在物化视图上插入和执行选择时，我得到了相同的旧数据？我需要手动刷新吗？
CREATE MATERIALIZED VIEW MVW_TEST
REFRESH FORCE ON DEMAND
START WITH TO_DATE('01-01-2009 00:01:00', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1/1152
As select * from TEST12
回答by Gaurav Soni
I have demonstrated in steps where a materialized view refresh after every one minute,for having
a mv which refresh after 5 minute use next(sysdate+5/1440)
我已经在步骤中演示了物化视图在每次one minute使用后刷新，因为有一个使用 5 分钟后刷新的 mv next(sysdate+5/1440)
Step1:
第1步：
Create table temp (A int);
Step2:
第2步：
Create Materialized view temp_mv
refresh complete start with (sysdate) next (sysdate+1/1440) with rowid
as select * from temp;
Step3:
第三步：
select count(*) from temp;
COUNT(*)
----------
0
Step4:
第四步：
select count(*) from temp_mv;
COUNT(*)
----------
0
Step5:
第五步：
begin
for i in 1..10 loop
insert into temp values (i+1);
end loop;
end;
/
Step6:
第六步：
commit;
Step7:
第七步：
select count(*) from temp;
COUNT(*)
----------
10
Step8:
第八步：
select count(*) from temp_mv;
COUNT(*)
----------
0
Step9:
第九步：
select to_char(sysdate,'hh:mi') from dual;
TO_CH
-----
04:28
Step10:
第十步：
select to_char(sysdate,'hh:mi') from dual;
TO_CH
-----
04:29
Step11:
第11步：
select count(*) from temp;
COUNT(*)
----------
10
Step12:
第12步：
select count(*) from temp_mv;
COUNT(*)
----------
10
回答by Demish Alex
You can also create a scheduler job:
您还可以创建调度程序作业：
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'DBAPER.MVW_TEST_REFRESH'
,start_date => TO_TIMESTAMP_TZ('2011/09/02 00:00:00.000000 US/Central','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=DAILY;BYHOUR=6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22;BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN
DBMS_MVIEW.REFRESH(''MVW_TEST'',''C'');
END;'
,comments => 'Job to refresh materialized view MVW_TEST.'
);
END;
/
That way you have more configuration options, like excluding night hours or weekends for example.
这样你就有更多的配置选项，比如排除夜间或周末。
