Using NEXT clause to set periodic materilized view refresh in oracle and verifying refresh

materialized-vieworacleoracle-apex

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>

Best Answer

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;'

Related Question