How To Identify the Database Links of a Failed Refresh Job

From time to time, we get DBMS_JOB failures for failed refreshes that happen due to broken database links.

The error in the alert.log is ORA-12012: error on auto execute of job {number}, followed by one of ORA-12547: TNS:lost contact, ORA-12170: TNS:Connect timeout occurred, ORA-12541: TNS:no listener, or any other of the network connection failures.

I usually have a quick look into DBA_JOBS to find out the materialized view (MV) name and then peak into the DBA_MVIEWS.MASTER_LINK column.

However, I have a client with refreshes scheduled using MV refresh groups, and it took me a while to recall the view name, asking around and RTFM’ing. Since this situation comes up regularly, I wrapped up a quick script that parses job content, takes in account MV refresh groups, and outputs the database link(s) involved. I think it could be useful to few others so here it goes:

with rv as
(select replace(replace(replace(what,
â€²dbms_refresh.refresh(â€²â€²â€³â€², â€²â€²), â€²â€³.â€³â€², â€².â€²), â€²â€³â€²â€²);â€²) v
from dba_jobs
where what like â€²dbms_refresh.refresh%â€²
and job=&job_no
),
rv2 as
(select substr(v,1,instr(v,â€².â€²)-1) o, substr(v,instr(v,â€².â€²)+1) v
from rv
)
select distinct master_link
from dba_mviews
where (owner,mview_name) in (select o, v from rv2)
union all
select distinct master_link
from dba_mviews
where (owner,mview_name) in
(select owner, name
from dba_refresh_children
where (rowner,rname) in (select o, v from rv2)
);

A few minutes later, I saw in our tracking system a reference (thanks to Marc Billette) to some documentation with few useful scripts for MV troubleshooting. The new result is much nicer:

Unfortunately, it seems that someone was messing around with the refresh jobs, and some of them were not associated with any refresh group in DBA_RGROUP. Now I have the task of cleaning up the refresh jobs mess, but that’s another story.

I decided I should leave the first query just in case any readers are in the same situation, even though it’s not very clear SQL compared to the second version. In addition, the first query works for both single MV refreshes and for refresh groups.