Oracle Materialized View Refresh Group - Montly Interval
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle Materialized View Refresh Group - Montly Interval

  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Oracle Materialized View Refresh Group - Montly Interval

    I have a few Materialized Views that I need to update weekly and monthly.

    So for the weekly I'm using:

    Code:
    execute DBMS_REFRESH.MAKE(
    name=>'LPMVRefresh_Weekly_1',
    list=>'mySchema.MV1_mv,mySchema.MV2_mv',
    next_date => TO_DATE('2013-09-08:00:30:00','YYYY-MM-DD:HH24:MI:SS'),
    interval =>'sysdate+7');
    How can I do so they refresh monthly, say every 1st of the month at 1 am? Is there something like Interval => sysdate+monthly?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    I have not used the DBMS_REFRESH.MAKE method, however, this will get you the next 1AM on the first of the month.

    SYS@rdbadb01 AS SYSDBA> select to_char(add_months(trunc(sysdate, 'MM'), 1)+1/24, 'DD-MON-YYYY:HH24:MI:SS') next_date from dual;

    NEXT_DATE
    ---------------------------------------------------------------------------------------
    01-OCT-2013:01:00:00

    Elapsed: 00:00:00.05
    this space intentionally left blank

  3. #3
    Join Date
    Jul 2013
    Posts
    4
    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width