Is there a timeout for a query over database link?

Question

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.

Motivation:
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.

Question:
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.

Answer 1

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.

Answer 2

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:

ALTER SESSION CLOSE DATABASE LINK dblink;

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.