Edit/Preface: This question has been migrated from SO as I am particularly interested on the question on timeouts on DB link queries. The provided workaround from SO is somewhat OK, but I'm really interested in the question itself.

I've had one query running "forever" (more than 2 days, until I killed the session), which was using a database link. Problem seemed to be that the remote database became unavailable and for some yet unkown reason no ORA-02068 was raised (not to be discussed here) and the query just waited and waited.

(The query is issued by a dbms_scheduler job, which executes a procedure in a PL/SQL package. As a consequence, the job was also stuck. But that's not of special interest for the core of this question)

I've simulated this situation by putting one of my test DBs in quiesce mode and queried it over a database link. As anticipated, the query was waiting until either manually canceled or the remote DB was unquiesced.

I have no control over the remote database's behavior and up-time, so I am in search for some possibility to set a timeout on a query which is using a database link.

I've already looked into profiles (CPU_PER_CALL etc), sqlnet.ora parameters, adding local naming parameters directly into the connect string (such as adding (connect_timeout=10) to the database link definition), running a command with ... for update wait 1, but they either work for busy or idle sessions, but not for sessions in wait.

So I am in search for some option on the "local" side of the database link, which kind of sets a timeout for queries over database links.
Some solution like alter session set xyz or select ... from a@b "wait 100" --(yes, I know this syntax doesn't exist) would be appreciated, as I have no DBA rights on these particular DBs.

I'm currently on 10gR2 but upgrading to 11gR2 in a few weeks, so ideas for any of these versions will be useful.

  • how is the query used? part of a larger procedure/package, underlying SQL for a mat view, run from an external app, ...? I don't know of a simple "wait xxx" syntax, your solution may need to be part of a larger program, depends on your usage tho.
    – tbone
    Jul 24, 2012 at 16:53
  • The query is issued by a dbms_scheduler job, which executes a procedure in a PL/SQL package. (updated question with that)
    – GWu
    Jul 24, 2012 at 18:05

2 Answers 2


Since you're using dbms_scheduler, you can set the max_run_duration attribute of the job to some limit, and then have the scheduler email you if that event is raised. Behind the scenes Oracle uses queueing tables (which can enable you to create jobs that fire when an event is raised, if you wanted to take the extra steps to do more automation around your response). But basically any job that runs over the max_run_duration you setup will raise event type: JOB_OVER_MAX_DUR

The email piece is built in 11gr2, see here for good writeup.

Hope that helps.

  • great idea, thanks! It's not exactly an answer to the specific question on the timeout on DB links, but I guess it solves my initial issue anyway, so +1 :-). I didn't know of max_run_duration and starting from this I just found how to stop&drop the hung job (forums.oracle.com/forums/thread.jspa?threadID=521939#2002982)
    – GWu
    Jul 24, 2012 at 18:57
  • For some reason I can't get max_run_duration to raise the event, so I forked a new question here
    – GWu
    Jul 25, 2012 at 8:58

I'm not saying I have a solution but I would also like more discussion on how to control a dblink timeout. I suppose it could be conceivable to write code that fires an event, or waits for a TNS timeout event, and then executes:


You can of course poll the remote server before with:

select * from dual@dblink

to check if it's available, but that doesn't fix the issue of code running for too long on the remote. The bad remote code should fire wait events, so I suppose those could be trapped (even at the class level in 12c). That still doesn't give us an elegant solution of forcing a dblink session to timeout.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.