Hello,
I have a procedure PR_REFRESH_MV which loops through all the MVs based on a given schema, as shown :-
SELECT OWNER, mview_name, compile_state, staleness, last_refresh_date
FROM user_mviews
ORDER BY last_refresh_date;
I would like to do the following:-
1. As it loops through each MV object (mview_name), for this current loop "parent" MV object, I would like to check to see if there are any nested dependent MVs (children and their children) which are dependent on this current MV object. I don't know how to identify the children dependent MV objects and nested MV objects.
2. If there are dependent MV objects found (direct child or any nested ones), then I would like to use the following procedure to not only refresh the current "parent" object but also go ahead and refresh ALL its dependent child MVs and their nested MVs, as shown:-
dbms_mview.REFRESH_DEPENDENT
(
number_of_failures => num_of_failures,
LIST => owner || '.' || mview_name,
method => 'C',
atomic_refresh => FALSE,
NESTED => TRUE --This will not only refresh the Parent MV but also all of its Children MVs and their nested Children MVs!
);
3. Else, if the current MV object being looped does NOT have any other dependent MVs (i.e. it is a purely stand-alone MV which has no dependent MVs or any nested MVs), then I would like to simply call the following:-
dbms_mview.REFRESH
(
LIST => owner || '.' || mview_name,
method => 'C',
atomic_refresh => FALSE
);
4. Given the nature of the main loop, you are bound to hit a child MV object which was already fully "refreshed" as a result of its parent being previously detected to have children (or nested) MVs and hence, the NESTED = TRUE condition already had refreshed this child MV's contents. So, if this is the case, I would like to skip refreshing this child MV completely (i.e. dbms_mview.REFRESH should not execute for this child MV since it was already refreshed in the earlier steps).
Any help would be appreciated.
Thanks!