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?

1 Answer 1


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

  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'))'
  • Thanks for the comment! Yes, I was able to get it to refresh weekly every sunday OR monthly, just not both. I want the MView to refresh every sunday AND every 1st of the month.
    – Diomedes
    Commented Sep 11, 2013 at 18:46
  • The answer is there, separate mutiple interval definitions with a comma Commented Aug 30, 2016 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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