🧩 How to Fix ORA-03135: Connection Lost Contact

🧩 Introduction

The error ORA-03135: connection lost contact occurs when a client loses its connection to the Oracle database unexpectedly. This issue is often frustrating for DBAs and developers because it may interrupt running queries, batch jobs, or application sessions. The error is related to network instability, timeouts, or database resource limits. Understanding the root cause is essential for a permanent fix.

Why This Error Happens

Several common reasons lead to ORA-03135:

Firewall or network timeout closing idle database sessions.

sqlnet.ora timeout settings such as SQLNET.EXPIRE_TIME or SQLNET.INBOUND_CONNECT_TIMEOUT.

Listener.ora restrictions that kill long or idle connections.

Resource limits like idle timeouts defined at the database profile level.

Database server overload causing connections to drop.

Client disconnection without proper logout.

Example of the Error

When a client issues a query and suddenly loses the session, you may see:

ORA-03135: connection lost contact

Process ID: 12345

Session ID: 678 Serial number: 91011

Step-by-Step Solutions

1. Check sqlnet.ora for timeout values

Locate the sqlnet.ora file in $ORACLE_HOME/network/admin/ and review the following parameters:

SQLNET.INBOUND_CONNECT_TIMEOUT = 60

SQLNET.OUTBOUND_CONNECT_TIMEOUT = 60

SQLNET.EXPIRE_TIME = 10

Increase values if they are too low.

Example:

SQLNET.INBOUND_CONNECT_TIMEOUT = 300

SQLNET.EXPIRE_TIME = 30

2. Verify listener.ora configuration

If listener.ora has timeouts defined, they may kill idle sessions. Increase or remove them:

INBOUND_CONNECT_TIMEOUT_LISTENER = 300

3. Check firewall or network devices

Some firewalls drop idle connections after a short time. Workarounds include:

Increasing SQLNET.EXPIRE_TIME so Oracle sends keep-alive packets.

Configuring the firewall to allow longer idle times.

4. Review database profile limits

Check if user profiles enforce an idle timeout:

SELECT profile, resource_name, limit

FROM dba_profiles

WHERE resource_name LIKE 'IDLE_TIME';

If idle timeouts are enforced, either increase the limit or assign users to a different profile:

ALTER PROFILE app_users LIMIT IDLE_TIME UNLIMITED;

5. Monitor system resources

High CPU, memory, or session limits may cause Oracle to drop connections. Check with:

SELECT resource_name, current_utilization, max_utilization, limit_value

FROM v$resource_limit;

6. Enable Oracle keep-alive at the OS level

In some cases, operating system TCP keep-alive settings should be tuned to ensure long-lived sessions stay active.

Best Practices

Always configure SQLNET.EXPIRE_TIME to prevent network devices from dropping connections.

Use proper connection pooling in applications instead of keeping idle connections open for a long time.

Coordinate with network administrators to align Oracle timeouts with firewall and load balancer settings.

Regularly monitor session drops in alert.log for early detection.

Conclusion

The error ORA-03135: Connection Lost Contact is often caused by timeout or networking issues rather than database corruption. By tuning sqlnet.ora, adjusting listener.ora, monitoring user profiles, and coordinating with network infrastructure, DBAs can minimize this error and provide stable connectivity for applications and users.