How to limit DB Link Connection Timeout

Hi,

I just want to share something small that I dug to learn. I didn’t work with db links for years and now all of a sudden I have tens of them. This comes with new challenges of course. When you tried to run a query or function/procedures over a db link, first db link connection must be created and this connection is just a normal database connection but let’s say database on the other side (remote database) is closed or you created db link with incorrect connection string (incorrect ip address or db service name etc). How long will you wait until you get an error that connection couldn’t be created? By default it is 120 seconds.

So you run “select * from dual@mydblink”  and if remote database has an issue then you must wait 120 seconds to get an error. if you try to connect via sqlplus on the same server, it will take 60 seconds to get an error. I don’t think that you want to increase this timeout threshold because waiting 60 seconds for a connection with modern technology is absurd. so if you want to reduce  the timeout time you must set SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter in your sqlnet.ora file. By default this parameter value is 60. that is why sqlplus took 60 seconds to wait for the connection and for some reason (which I don’t know) db link connection doubles this timeout and wait for 120 seconds. if you set outbound_connect_timeout parameter to 10, then sqlplus will return the timeout error (ORA-12170: TNS:Connect timeout occurred) in 10 seconds and db link connections will return in 20 seconds in case of a initial connection failure.

wish you all a healthy/nice day.