DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12163 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12163: TNS Connect Descriptor Is Too Long — Causes, Fixes & Prevention

ORA-12163 is an Oracle Net Services (TNS) error that occurs when a connect descriptor exceeds Oracle's internally allowed maximum length. This typically happens during connection attempts from clients using tnsnames.ora, Easy Connect strings, or LDAP-based naming. If you've hit this error, your connection string is simply too long for Oracle to process — and the fix is usually straightforward.

Top 3 Causes

1. Overly Complex tnsnames.ora Descriptor

The most common cause is a TNS alias definition in tnsnames.ora that includes too many ADDRESS entries, long fully-qualified hostnames, or deeply nested FAILOVER_MODE blocks.

-- Check current service and host details after connecting
SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name,
       SYS_CONTEXT('USERENV', 'SERVER_HOST')  AS server_host,
       SYS_CONTEXT('USERENV', 'INSTANCE_NAME') AS instance_name
FROM DUAL;

-- Review registered services in the database
SELECT NAME, NETWORK_NAME
FROM DBA_SERVICES
ORDER BY NAME;
Enter fullscreen mode Exit fullscreen mode

Fix: Shorten hostnames, reduce the number of ADDRESS entries, and remove redundant parameters.

-- Simplified tnsnames.ora entry (recommended structure)
/*
MYDB =
  (DESCRIPTION =
    (FAILOVER = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv2)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = mydb))
  )
*/
Enter fullscreen mode Exit fullscreen mode

2. Hardcoded Long Connection Strings in Application Code

Applications that embed a full DESCRIPTION block directly in JDBC URLs or connection pool configurations can easily exceed Oracle's length limit, especially in RAC environments.

-- After fixing, verify the service name your app should use
SELECT NAME, NETWORK_NAME, ENABLED
FROM DBA_SERVICES
WHERE ENABLED = 'YES'
ORDER BY NAME;

-- Check listener and remote listener settings
SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME IN ('local_listener', 'remote_listener', 'service_names');
Enter fullscreen mode Exit fullscreen mode

Fix: Replace inline descriptors with a short TNS alias defined in tnsnames.ora.

-- BAD: Full descriptor hardcoded in connection URL
-- jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)
--   (HOST=very-long-hostname.internal.corp.com)(PORT=1521))
--   (CONNECT_DATA=(SERVICE_NAME=svc.internal.corp.com)))

-- GOOD: Use a TNS alias instead
-- jdbc:oracle:thin:@MYDB
Enter fullscreen mode Exit fullscreen mode

3. sqlnet.ora Domain Settings Inflating Descriptor Length

If NAMES.DEFAULT_DOMAIN is set in sqlnet.ora and your TNS alias already contains a domain suffix, Oracle appends the domain again — doubling the length and potentially triggering ORA-12163.

-- Check DB domain parameter
SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME IN ('db_domain', 'db_name');

-- Check domain context
SELECT SYS_CONTEXT('USERENV', 'DB_DOMAIN') AS db_domain,
       SYS_CONTEXT('USERENV', 'DB_NAME')   AS db_name
FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Fix: Remove duplicate domain suffixes from your TNS alias, or clear NAMES.DEFAULT_DOMAIN in sqlnet.ora if it's not needed.

-- sqlnet.ora fix example
/*
# Remove or comment out if aliases already include domain
# NAMES.DEFAULT_DOMAIN = corp.com

# Keep resolution path simple
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
*/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist 

-- Step 1: Confirm which parameters affect naming
SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME IN ('service_names', 'db_domain', 'local_listener', 'remote_listener');

-- Step 2: Validate active services
SELECT INST_ID, NAME, NETWORK_NAME
FROM GV$SERVICES
ORDER BY INST_ID, NAME;

-- Step 3: Confirm connection metadata post-fix
SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS svc,
       SYS_CONTEXT('USERENV', 'SERVER_HOST')  AS host
FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Use tnsping <alias> from the OS command line to validate your descriptor before connecting:

-- Run from terminal (not inside SQL*Plus)
-- $ tnsping MYDB
-- $ tnsping MYDB 3
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Standardize and audit tnsnames.ora regularly.
Keep hostnames short (use aliases or IPs), limit ADDRESS entries to what's strictly necessary, and remove stale entries. Integrate tnsping checks into your deployment pipeline to catch issues before they reach production.

2. Use Oracle Connection Manager (CMAN) for complex topologies.
In RAC or multi-node environments, route all client connections through CMAN. Clients only need CMAN's single address, keeping every connect descriptor short and ORA-12163-proof. For Oracle 19c+, also explore Easy Connect Plus syntax for cleaner, more manageable connection strings.

Related Errors

Error Code Description
ORA-12154 TNS alias not found — check spelling and tnsnames.ora path
ORA-12170 Connection timeout — firewall or listener issue
ORA-12514 Listener doesn't know the requested service
ORA-12533 Illegal ADDRESS parameters in descriptor

📖 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.

Top comments (0)