Oracle database connection instant timeout

Question

I have to check automatically if a connection to another database in oracle is vaild. I want a trigger to perform different actions based on whether the connection is vaild while it's running or not. It takes about 40 seconds every time to return ORA-12170 (Connection timeout) if i try to check the connection by using a query including the database link to the remote database. Is there any way to set a much shorter time interval for the timeout or to abort the query if it takes that much time? I just want to get a quick response if the connection is working. The trigger is called several times every view seconds, so i can't wait for 40 seconds every time it's called when the connection is interrupted. Im using Oracle XE 11g.

• Husam Mohamed: The interval seems very short, is it really required to check the connection this number of times between every few seconds?
• BriteSponge: Perhaps if you say why you need to check the Oracle connection every few seconds it might help to pinpoint a solution.
• Gandalf The Gay: I have to check it every time because i have to move data from one database to the other every time something is inserted in the local datatable. If the connection gets interrupted all the data sould be stored in the local datatable instead. It takes 40 seconds to get something (the error) back if there is no connection. Unfortunately this causes massive data loss because it prevents new data from being inserted into the local datatable while the query is still processing to return the connection error.
• EdStevens: It sounds to me like you're looking at things through the wrong end of the telescope. WHY do you have to MOVE data every time something is inserted? It really, really sounds like you are trying to implement a sub-optimal solution to some as-yet-unspecified business problem. I'll bet if you focus on the business need instead of the technique, you could find a much simpler solution.

Answer 1

That timeout can be configured with the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter.

For example, place the below in sqlnet.ora, which results in outbound connections timing out after 10 seconds without answer:

SQLNET.OUTBOUND_CONNECT_TIMEOUT=10

• Amit Naidu: +1 and just in case someone is wondering where that sqlnet.ora file is, look in: $ORACLE_HOME\network\admin