I am not able to find the right documentation on setting specific timings for materialized view refresh for oracle. I read up the documentation and some examples there, however there is no definite documentation on using NEXT clause. What would I do to set up refresh every day at 8am and 8pm for example? How would I achieve refresh 3 times a day?
Here's what I am using in my case. I understand this will start refresh at 7am tomorrow and next every 12 hours?
alter materialized view MY_VIEW refresh fast start with (sysdate+1) + 7/24 next trunc(sysdate) + ((trunc(to_char(sysdate,'HH24')/12)*12)+12)/24
I also want to know if there is a way to know how to verify if the materialized view was refreshed once the timings are set.
Update: I tried to refresh my MV every hour, which didi not work. I am wondering why is that. Here's what I used:
CREATE MATERIALIZED VIEW "MYVIEW" ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "APEX_xxxxxxxxxxxxxxxxxx" BUILD IMMEDIATE USING INDEX REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate+1/24 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS select * from <mydblinktable>