1

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>
1
  • sysdate + 0.5 should do the same. 0.5 days = 12 hours. But be aware, time is calculated after job is finished, so refresh time will shift forwards by time. Jun 19 '17 at 9:46
5

I'd use DBMS_SCHEDULER instead, with a job for each refresh. It's then easier to manage, and view previous executions.

CREATE OR REPLACE PROCEDURE REFRESH_MY_VIEW
AS
BEGIN
    DBMS_MVIEW.REFRESH('MY_VIEW');
END;
/

Refresh at 8am every day:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'REFRESH_MY_VIEW',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'REFRESH_MY_VIEW',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP, 
    repeat_interval => 'freq=daily; byhour=8; byminute=0; bysecond=0;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'Refreshes MY_VIEW at 8am'
    );
END;
/

Refresh at 8pm every day:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'REFRESH_MY_VIEW',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'REFRESH_MY_VIEW',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP, 
    repeat_interval => 'freq=daily; byhour=20; byminute=0; bysecond=0;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'Refreshes MY_VIEW at 8pm'
    );
END;
/

The *_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS data dictionary views provide all the information you need to track what executed, when, and if any errors occurred. Documentation link here.

As pointed out in a comment, you can add multiple by hour clauses, comma-separated, as follows: repeat_interval => 'freq=daily; byhour=8,20; byminute=0; bysecond=0;'

7
  • 1
    Okay, this sounds like a good option too. Any particular reason why you would prefer DBMS Scheduler instead of auto-refresh on materialized views? One more thing, after posting this question, I tried refreshing my view every hour, which did not working. I will surely try dbms scheduler, but I am trying to understand why my MV auto-refresh did not work. I am updating my question with my code. Jun 16 '17 at 7:46
  • @SejalParikh refresh groups are best for 1 thing: data consistency between related mviews. An implicit commit is performed before/after DBMS_MVIEW.REFRESH is called, so data inconsistency can come into play if you have multiple mviews with PK/FK relationships. If you do have that need, then refresh groups solves that problem since the entire group is atomic. Barring that, dbms_scheduler is easier to use, more versatile, more logging; Overall, a better approach to mview refreshes. I've even had refresh groups stop refreshing for no apparent reason... a simple re-call solves those hiccups. Jun 16 '17 at 21:02
  • @SejalParikh Oh, using the NEXT clause automatically creates a refresh group with the same name as the mview to perform the mview refresh on the NEXT schedule, so if you are confused when I say "refresh group", we are still talking about the same thing... Jun 16 '17 at 21:09
  • You need only one schedule: repeat_interval => 'freq=daily; byhour=8,20; byminute=0; bysecond=0;' Jun 19 '17 at 9:07
  • @WernfriedDomscheit I didn't know you could do that, thanks
    – Philᵀᴹ
    Jun 19 '17 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.