Oracle ORA-12150 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12150: TNS Unable to Send Data — Causes, Fixes, and Prevention

ORA-12150 occurs when Oracle Net (TNS) successfully establishes a connection between client and server but then fails to transmit data packets during an active session. Unlike ORA-12541 (no listener), the connection itself is alive — the problem lies in the data transport layer. This error is commonly triggered by network instability, firewall session timeouts, or misconfigured Oracle Net parameters.

Top 3 Causes and Fixes

1. Firewall / Load Balancer Idle Session Timeout

The most common culprit. Firewalls and load balancers silently drop TCP connections that appear idle for too long. Oracle sessions running long queries or sitting idle exceed the firewall's timeout threshold, and the next data send attempt fails with ORA-12150.

Fix: Enable Dead Connection Detection (DCD) in sqlnet.ora so Oracle sends periodic probe packets to keep the connection alive.

-- Add to $ORACLE_HOME/network/admin/sqlnet.ora
-- SQLNET.EXPIRE_TIME = 10    (probe every 10 minutes)

-- Identify long-running idle sessions that are at risk
SELECT sid,
       serial#,
       username,
       status,
       machine,
       last_call_et AS idle_seconds,
       program
FROM   v$session
WHERE  type        = 'USER'
  AND  status      = 'INACTIVE'
  AND  last_call_et > 300   -- idle more than 5 minutes
ORDER BY last_call_et DESC;

-- Check current network-related parameters
SELECT name, value
FROM   v$parameter
WHERE  name IN ('local_listener', 'remote_listener')
ORDER BY name;

2. Misconfigured sqlnet.ora Timeout Parameters

Overly aggressive SQLNET.SEND_TIMEOUT or SQLNET.RECV_TIMEOUT values can abort data transfers before they complete, especially during bulk operations or across high-latency WAN links.

Fix: Review and tune timeout parameters. Ensure timeouts are generous enough for your workload.

-- Verify active network wait events to detect timeout patterns
SELECT event,
       total_waits,
       total_timeouts,
       ROUND(time_waited / 100, 2) AS time_waited_secs,
       ROUND(average_wait / 100, 2) AS avg_wait_secs
FROM   v$system_event
WHERE  event LIKE '%SQL*Net%'
ORDER BY time_waited DESC
FETCH FIRST 5 ROWS ONLY;

-- Confirm session-level network statistics
SELECT sn.name, ss.value
FROM   v$statname sn
JOIN   v$sesstat  ss ON sn.statistic# = ss.statistic#
JOIN   v$session  s  ON ss.sid = s.sid
WHERE  s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID')
  AND  sn.name IN (
         'bytes sent via SQL*Net to client',
         'bytes received via SQL*Net from client',
         'SQL*Net roundtrips to/from client'
       );

Recommended sqlnet.ora baseline settings:

SQLNET.EXPIRE_TIME   = 10
SQLNET.SEND_TIMEOUT  = 60
SQLNET.RECV_TIMEOUT  = 60
TCP.CONNECT_TIMEOUT  = 15

3. Undersized SDU (Session Data Unit) or OS TCP Buffer

When transferring large result sets, an undersized SDU forces excessive packet fragmentation, increasing the risk of transmission errors. Similarly, OS-level TCP send/receive buffer limits can throttle throughput and cause failures under high concurrency.

Fix: Increase SDU size in both tnsnames.ora and listener.ora, and verify OS TCP buffer settings with your system administrator.

-- tnsnames.ora entry with tuned SDU
-- PRODDB =
--   (DESCRIPTION =
--     (SDU = 65535)
--     (ADDRESS = (PROTOCOL=TCP)(HOST=db-host)(PORT=1521))
--     (CONNECT_DATA =
--       (SERVICE_NAME = proddb)
--       (SERVER = DEDICATED)
--     )
--   )

-- Confirm connection details for the current session
SELECT SYS_CONTEXT('USERENV', 'DB_NAME')      AS db_name,
       SYS_CONTEXT('USERENV', 'SERVER_HOST')  AS server_host,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS')   AS client_ip,
       SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name
FROM DUAL;

-- Monitor top network waits at instance level
SELECT event,
       total_waits,
       total_timeouts,
       time_waited
FROM   v$system_event
WHERE  event LIKE '%SQL*Net%'
   OR  event LIKE '%TCP%'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

Prevention Tips

Standardize sqlnet.ora across all environments. Always include SQLNET.EXPIRE_TIME in production sqlnet.ora files. Version-control these files in Git so changes are tracked and auditable.
Monitor network wait events proactively. Schedule weekly AWR report reviews focusing on the Top Network Events section. Set up OEM alerts when SQL*Net wait times exceed acceptable thresholds, catching degradation before it becomes an outage.

Related Errors

Error Code	Description
ORA-12535	TNS operation timed out
ORA-12541	TNS no listener
ORA-03113	End-of-file on communication channel
ORA-03114	Not connected to ORACLE
ORA-12537	TNS connection closed

📖 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