How To Identify the Database Links of a Failed Refresh Job

Posted in: Technical Track

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:

SELECT DISTINCT mv.master_link
  FROM dba_rgroup rg, dba_refresh_children rc, dba_mviews mv
 WHERE rg.job = &job_no
   AND rc.rowner = rg.owner
   AND rc.rname =
   AND rc.type = 'SNAPSHOT'
   AND mv.mview_name =
   AND mv.owner = rc.owner;

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.

Interested in working with Alex? Schedule a tech call.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *