Printable View
I have 5 MViews that I want to refresh in two occassions, every sundays and at the 1st of the month. I created a Refresh Group for the weekly and that work fine. But when I tried to created the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".
You can only have a materialized view in one refresh group? What options to I have to refresh it in different intervals?
Thanks
You can use DBMS_REFRESH.CHANGE to change the refresh interval.
A job can be scheduled to DBMS_REFRESH.CHANGE back and forth from every-Sunday to 1st-of-the-month schedules.
Pablo, is changing the schedule really the best solution?Quote:
Originally Posted by ModelosPR
What about creating a function to find the next date?
:DCode:CREATE OR REPLACE FUNCTION get_next_run_date
RETURN DATE
AS
v_run_Date DATE;
BEGIN
SELECT MIN(next_run_date)
INTO v_run_Date
FROM ( SELECT TRUNC(NEXT_DAY( SYSDATE, 'SUNDAY'))+1/24 next_run_date FROM dual
UNION ALL
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)+1/24 next_run_date FROM dual );
RETURN v_run_Date;
END;
/
SQL> SELECT TO_CHAR(next_run_date, 'DD-MON-YYYY:HH24:MI:SS') next_run_date
FROM ( SELECT TRUNC(NEXT_DAY( SYSDATE, 'SUNDAY'))+1/24 next_run_date FROM dual
UNION ALL
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)+1/24 next_run_date FROM dual );
2 3 4
NEXT_RUN_DATE
---------------------------------------------------------------------------------------
15-SEP-2013:01:00:00
01-OCT-2013:01:00:00
Gandolf - my solution works and it is functional; yours works and it is poetry :-)Quote:
Originally Posted by gandolf989
That is kind of you to say. :cool:Quote:
Originally Posted by PAVB