java.sql.SQLException: Io exception: Socket read timed out vs Closed Connection

Question

I am trying to research this issue on the following two errors connecting to Oracle DBs:

1. Closed Connection
2. java.sql.SQLException: Io exception: Socket read timed out

My understanding:

1. Closed Connection : Is occurring either because there was some sort of network disruption or the DB closed the session due to some sort "inactivity"
2. java.sql.SQLException: Io exception: Socket read timed out : This is a case where the connection was made successfully but for some reason the socket/data was empty and eventually it timed-out because no data was available.

Is it possible to replicate the above errors in a local Oracle DB env ? What are the steps ?

I appreciate you taking the time to respond.

Thanks.

Answer 1

Your understanding on closed connection is right. reason for closed connection: External devices such as firewall, network devices, and remote database listeners can force network connections to close after a period of inactivity

ReadTimeOut will happen even on active connections. If a query or procedure is taking lot of time, you will get read time out exception.

• Closed Connection : Shutdown the database listener when database is running
• ReadTimedOut : Add sleep in procedure for more than 10 minutes and call that procedure from application

Replication of Socket read time out error in Oracle DB env:

1. setNetworkTimeout for SQL connection // for example sake, set timeout as 120 seconds

2. Call a database procedure from java and sleep in that procedure for time more than setNetworkTimeout

   dbms_lock.sleep(125); -- sleeps for 125 seconds
   

Since procedure is not returning with-in 120 seconds due to 125 seconds sleep, java will throw socket read time out in above scenario.