One Materialized View in Two Refresh Groups

materialized-vieworacle

I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to create the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".

You can have a materialized view in only one refresh group?

What options do I have to refresh it in different intervals?

Best Answer

I believe that such query will return the next run date you require:

select least(TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH'),
  next_day(sysdate, 'Sunday')) as answer from dual;

It calculates dates of next Sunday and 1st of next month and then returns the one which will be sooner.

Just 'Sunday' in next_day is NLS dependent.

That should give your required interval in call to DBMS_REFRESH.MAKE procedure:

interval=>'least(TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH'), next_day(sysdate, 'Sunday'))'
Related Question