Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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) 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.

share|improve this question

migrated from stackoverflow.com Jul 26 '12 at 12:40

This question came from our site for professional and enthusiast programmers.

    
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 '12 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 '12 at 18:05
add comment

1 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.

share|improve this answer
    
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 '12 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 '12 at 8:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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