Oracle SQL Developer连接异常求助：IO Error: An established connection was aborted by the software in your host machine

Got it, let's tackle this connection abort issue you're facing with overnight queries in Oracle SQL Developer. I've dealt with this exact problem a few times, so here are some straightforward fixes and settings tweaks to keep your connection alive:

1. Adjust Connection Timeout Parameters in SQL Developer #

First, tweak the connection properties to disable or extend timeouts:

• Right-click your database connection in SQL Developer and select Properties
• Switch to the Advanced tab
• In the Connection Properties section, add these two key-value pairs:
  • oracle.net.CONNECT_TIMEOUT=0 (0 means no timeout; you can also set a large value like 3600000 for 1 hour)
  • oracle.net.READ_TIMEOUT=0 (disables the timeout for reading data from the server)
• Save the changes and reconnect. These parameters prevent SQL Developer from dropping the connection due to long query execution times.

2. Enable Keep-Alive in SQL Developer #

SQL Developer has a built-in feature to send periodic "ping" requests to keep the connection active:

• Go to Tools > Preferences from the top menu
• Expand Database > Advanced
• Check the box for Enable keep-alive
• Set a reasonable Keep-alive interval (e.g., 300 seconds / 5 minutes) — this tells SQL Developer to send a small request to the server regularly, so firewalls or network devices don't mark the connection as idle and drop it.

3. Database-Side Adjustments (If You Have DBA Access) #

If you can modify database settings, these changes can help:

• Check SQLNET.EXPIRE_TIME in sqlnet.ora: This parameter makes the database send periodic checks to validate client connections. Set it to a value like 10 (minutes) to prevent idle connections from being terminated by network equipment.
• Remove IDLE_TIME Limits: If your user profile has an IDLE_TIME restriction, it will disconnect idle sessions. Run this command to remove the limit:

  ALTER PROFILE [your_profile_name] LIMIT IDLE_TIME UNLIMITED;
  

4. Bonus Tips for Stability #

• Run Queries in Background: Right-click your query window and select Run in Background. This executes the query independently of the main UI, reducing the chance of connection drops due to UI-related issues.
• Check Network Firewalls: Sometimes company firewalls or routers automatically kill long-idle connections. The keep-alive feature mentioned earlier will bypass this by making the connection appear active.

内容的提问来源于stack exchange，提问作者Kyle Strougo