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?

  • 2
    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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.