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) and
sqlnet.ora parameters, 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.