-
Materialized View in more than one Refresh Group
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 (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by ModelosPR
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
Pablo, is changing the schedule really the best solution?
What about creating a function to find the next date?
Code:
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
-
Originally Posted by gandolf989
Pablo, is changing the schedule really the best solution?
What about creating a function to find the next date?
Gandolf - my solution works and it is functional; yours works and it is poetry :-)
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
Gandolf - my solution works and it is functional; yours works and it is poetry :-)
That is kind of you to say.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|