Oracle ORA-12153 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12153: TNS: Not Connected — Causes, Fixes, and Prevention

ORA-12153 is an Oracle Net Services (TNS) error that occurs when a client application attempts to execute a database operation without an active connection to the Oracle server. In simple terms, the network session between the client and the Oracle database has either never been established or has been silently dropped. This error is commonly seen in application servers, JDBC/ODBC environments, SQL*Plus sessions, and connection pool scenarios.

Top 3 Causes

1. Network Disconnection or Session Timeout

The most frequent cause is a firewall or network device silently dropping idle TCP connections without notifying either the client or the server. Connection pools often hold stale connections that appear valid internally but are actually dead at the network level.

Check for inactive sessions on the database side:

-- Find long-idle user sessions
SELECT sid,
       serial#,
       username,
       status,
       machine,
       last_call_et AS idle_seconds
FROM   v$session
WHERE  type = 'USER'
AND    status = 'INACTIVE'
ORDER  BY idle_seconds DESC;

-- Kill a specific stale session
ALTER SYSTEM KILL SESSION '145,2381' IMMEDIATE;

2. Incorrect TNS Configuration or Listener Issues

A misconfigured tnsnames.ora (wrong HOST, PORT, or SERVICE_NAME) or a stopped Oracle Listener will prevent connections from being established, resulting in ORA-12153.

Verify the listener and service names from within the database:

-- Check active database services
SELECT name,
       network_name
FROM   v$active_services
ORDER  BY name;

-- Confirm the service_names parameter
SELECT name, value
FROM   v$parameter
WHERE  name = 'service_names';

-- Confirm instance is up and reachable
SELECT instance_name,
       host_name,
       status
FROM   v$instance;

3. Server-Side Resource Exhaustion (PROCESSES / SESSIONS Limit)

When Oracle's PROCESSES or SESSIONS initialization parameters are maxed out, new connection requests are refused and existing sessions can become unstable.

Check current resource utilization:

-- Review processes and sessions limits vs. current usage
SELECT resource_name,
       current_utilization,
       max_utilization,
       limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions');

-- Increase the PROCESSES limit if needed (requires restart)
ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;

-- Generate kill commands for idle sessions over 1 hour
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
       AS kill_command
FROM   v$session
WHERE  type         = 'USER'
AND    status       = 'INACTIVE'
AND    last_call_et > 3600
AND    username IS NOT NULL;

Quick Fix Solutions

Reconnect the session — The simplest immediate fix is to close and reopen the database connection from the client side.
Restart the listener — Run lsnrctl stop followed by lsnrctl start on the database server to reset listener state.
Kill stale sessions — Use the ALTER SYSTEM KILL SESSION command shown above to clean up dead connections server-side.
Validate tnsnames.ora — Double-check HOST, PORT, and SERVICE_NAME entries match the actual database configuration.

Prevention Tips

Enable Dead Connection Detection (DCD):
Add SQLNET.EXPIRE_TIME = 10 to $ORACLE_HOME/network/admin/sqlnet.ora on the server. This causes Oracle to send a probe packet to clients every 10 minutes, automatically cleaning up dead connections before they cause errors.

Use connection validation in your connection pool:
Always configure your connection pool (HikariCP, DBCP, WebLogic, etc.) with a validation query so that stale connections are detected and replaced before being handed to the application.

-- Standard validation query used in connection pool configuration
SELECT 1 FROM dual;

Monitor resource limits proactively:

-- Run this regularly to catch resource exhaustion early
SELECT resource_name,
       current_utilization,
       limit_value,
       ROUND(current_utilization / TO_NUMBER(limit_value) * 100, 1)
         AS pct_used
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions')
AND    limit_value    != 'UNLIMITED';

Related Errors

ORA-12541 — TNS: no listener (listener is not running)
ORA-12514 — TNS: listener does not know of service requested
ORA-12170 — TNS: connect timeout occurred
ORA-03113 — End-of-file on communication channel (server process crash)
ORA-03114 — Not connected to Oracle (nearly identical symptom to ORA-12153)

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community