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?
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 asSELECT 'UP' from DUAL@REMOTEDB. I suspect this is firewall timeout issues anyway :) (Normally is) – Phil Aug 22 '12 at 18:26