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?