CODEWITHSUNDEEP
sqloracle11goracle12c
Anirudh D
Anirudh D

Reputation: 231

Refresh Materialized View every minutes but only between particular time of day

I have materialized view which I wanted to refresh every 15 minutes between 7 a.m. and 11 p.m.

Could you please help me with frequency clause of the same ?

CREATE MATERIALIZED VIEW ABC
NOCOMPRESS LOGGING
BUILD IMMEDIATE
USING INDEX 
REFRESH COMPLETE ON DEMAND START WITH ----- NEXT -----
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS select * from PQR

Best Regards

Upvotes: 2

Views: 1128

Answers (1)

XING
XING

Reputation: 9886

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(''ABC'',''C'');
                            END;'
      ,comments        => 'Job to refresh materialized view ABC.'
    );
END;
/

That way you have more configuration options, like excluding night hours or weekends for example.

Upvotes: 1

Related Questions