| How to create Dependency in MV [message #649708] |
Mon, 04 April 2016 07:43  |
 |
irish
Messages: 5
Registered: April 2016
|
Junior Member |
|
|
Hi Good Morning.!!!
I am Irisha and working as oracle developer with 1.7 yr exp. New to this forum. Need your help on below.
I have 2 materialized view and have Refresh scheduled like below
1) MV_COT_REFRESH 9 PM EST
2) MV_CUST_REFRESH 10 PM EST
Now, i have requirement to create dependency between these two MV's
IF first one completes then only need to start the Second one. Is there any way we can do in oracle.
Please need your help on this.
Thank you
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to create Dependency in MV [message #649969 is a reply to message #649832] |
Mon, 11 April 2016 10:42  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Only use the job chain if they have to run at 9am and then 10am. If the second one is only run at 10am to make sure that the first update has been completed then you can set it up 2 ways. Use a refresh group.
make the group by using the following call. For purposes of this task, I will call the refresh group MY_REFRESH. You can call it anything.
EXEC dbms_refresh.make('MY_REFRESH');
Now add the two Mviews into the group
EXEC DBMS_REFRESH.ADD(NAME => 'MY_REFRESH',LIST => 'MV_COT_REFRESH,MV_CUST_REFRESH',LAX => TRUE, nested => TRUE);
Now setup a dbms_job telling it to run everyday at 9am
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'dbms_refresh.refresh(''MY_REFRESH'');'
,next_date => to_date('12/04/2016 09:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => '(TRUNC(SYSDATE)+1)+ 9/24'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
It will now run your refresh in the correct order (uses dependencies) for both jobs and if the first one blows, it will not run the second one.
Also are you doing a complete refresh or a fast refresh. It makes a big difference in the refresh. To use the fast refresh, you would have to set up a MVIEW log on the original base table that the MV_COT_REFRESH uses. You might also be able to pull from the base tables that MV_COT_REFRESH uses for the MV_CUST_REFRESH if you have set up the MVIEW logs.
[Updated on: Mon, 11 April 2016 12:37] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
