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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We are trying to verify if a database link is up before inserting data into a remote database. We have no control over the remote database's behavior and status. We need to set a timeout on a query which is using a database link before deciding whether we can insert data.

Currently when the link or the remote database goes down in the middle of a transaction, the user has to wait for up to 20 minutes before regaining control on the session. We want to set the limit to a minute or less.

We're running Oracle 10gR2.

How can we set a timeout for a query like this?

share|improve this question
Do you mean it times out whilst doing the INSERT, or are you pulling data from the remote DB before inserting back? Can you edit your question with a timeline of the local<>remote behaviour please? Are you building some kind of staging table that can be persisted locally before inserting? Checking if the remote DB is up is as simple as SELECT 'UP' from DUAL@REMOTEDB. I suspect this is firewall timeout issues anyway :) (Normally is) – Phil Aug 22 '12 at 18:26

Your Answer


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

Browse other questions tagged or ask your own question.