Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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'))'
