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.
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: