Hi
We have a setup where we must refresh a set of Materialized Views overnight that are largely based on remote DB objects accessed via a DB Link. The routine that is called is similar to this:
procedure refresh_mv(p_err out varchar2)
as
pragma autonomous_transaction;
begin
execute immediate 'begin dbms_mview.refresh(:1,atomic_refresh=>false); exception when others then raise; end;' using g_ds_component_name;
exception
when others
then
p_err := dbms_utility.format_error_backtrace||' '||dbms_utility.format_error_stack||' '||sqlerrm;
rollback;
raise mv_failure;
end refresh_mv;
This is called in a loop from a calling procedure that is supposed to trap any errors and log them so we have visibility of all failures. This seemed to work fine, including logging of failures until I ran across the following error:
Error starting at line : 1 in command -
begin
pkg_refresh.stage_data;
end;
Error report -
ORA-12008: error in materialized view refresh path
ORA-00600: internal error code, arguments: [qcsprfro_tree:jrs present], [0xFFFFFFFF74CDE5A0], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2199
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2778
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1
- 12008. 00000 - "error in materialized view refresh path"
*Cause: Table SNAP$_<mview_name> reads rows from the view
MVIEW$_<mview_name>, which is a view on the master table
(the master may be at a remote site). Any
error in this path will cause this error at refresh time.
For fast refreshes, the table <master_owner>.MLOG$_<master>
is also referenced.
*Action: Examine the other messages on the stack to find the problem.
See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
<mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
still exist.
This happened whilst testing so the outer routine was called from an anonymous block. The error was not handled at all and the processing halted, leaving the log records open. Is there something about the type of error that was fatal to the overall process i.e. making it bypass the exception blocks? I'm guessing this may be connected to the remote nature of the source objects but I'd still expect to be able to trap any type of error and allow my local processing to continue to completion.
Does anyone have any similar experience?