Oracle connection and session exhaustion: PROCESSES, SESSIONS, and pool sizing

New Oracle connections start failing with ORA-00020 (maximum number of processes exceeded) or ORA-00018 (maximum number of sessions exceeded). The listener still answers TCP on 1521 but refuses new connections with TNS-12516 or TNS-12519 because the instance has no free handler to hand over. Existing sessions often keep working, masking the problem until an application tier’s pool needs to grow or refresh and fails.

This is a cliff-edge failure. The database works normally at 99% of the PROCESSES limit and hard-fails at 100%. There is no graceful degradation and no backpressure. The first you hear of it is frequently a user-facing outage when an app tier’s pool churns.

The hard limits are the PROCESSES and SESSIONS initialization parameters. PROCESSES caps OS processes (one per dedicated server plus background). SESSIONS derives from PROCESSES by default. Both are visible live in V$RESOURCE_LIMIT, where CURRENT_UTILIZATION shows what is happening now and MAX_UTILIZATION shows the high-water mark since instance startup. If MAX_UTILIZATION has ever touched LIMIT_VALUE, you already silently refused connections at some point.

What this means

PROCESSES is a hard ceiling on the number of OS processes Oracle will spawn, including background processes (DBWn, LGWR, PMON, ARCn, MMON, and the rest) and every dedicated server process behind a user session. SESSIONS derives from PROCESSES by default: Oracle 11.1 documents (1.1 * PROCESSES) + 5, while 12.1 and later document (1.5 * PROCESSES) + 22; verify the value on your release and patch level. Background-process slot consumption is workload-, platform-, and option-dependent; plan for roughly 30 to 60 slots before user sessions unless measurements say otherwise, so PROCESSES 300 may leave only the low-to-mid 200s for users.

Hitting either limit refuses new connections. The error surfaces differently depending on where you observe it:

• ORA-00020 from the database when PROCESSES is exhausted.
• ORA-00018 when SESSIONS is exhausted (rarer, because SESSIONS auto-derives above PROCESSES).
• TNS-12516 or TNS-12519 from the listener when the instance cannot register a free handler.
• Application-side errors: pool-exhaustion messages, connect timeouts, or ORA-03113/ORA-03135 if a stale connection was silently dropped by a firewall.

MAX_UTILIZATION is the key diagnostic column. It resets only on instance startup. If MAX_UTILIZATION equals LIMIT_VALUE at any point since startup, connections were already refused, even if current utilization is now low. The system already hit the wall, and the next spike will hit it again.

PROCESSES is static and requires an instance restart to change. SESSIONS is static in non-CDB and in CDB$ROOT. From multitenant Oracle releases documented with PDB-scoped parameter support, SESSIONS can be changed at PDB scope with ALTER SYSTEM without bouncing the CDB, but the CDB-level PROCESSES ceiling still applies.

flowchart TD
    A["New connection refused
ORA-00020 / TNS-12519"] --> B{"V$RESOURCE_LIMIT
CURRENT near LIMIT?"}
    B -- No --> C["Check OS limits
ulimit -u, pid_max"]
    B -- Yes --> D{"MAX_UTILIZATION
= LIMIT at peak?"}
    D -- Yes --> E["Limit already hit
resize PROCESSES (restart)"]
    D -- No --> F["Group V$SESSION
by MODULE / MACHINE"]
    F --> G{"One consumer
dominates?"}
    G -- "Pool max too large" --> H["Cap pool maxima
across app tier"]
    G -- "Idle, old LOGON_TIME" --> I["Connection leak
KILL SESSION"]
    G -- "enq: TX waits" --> J["Lock cascade
find blocker"]
    G -- "Jnnn / scheduler" --> K["Cap job parallelism"]

Common causes

Quick checks

Read-only triage. All safe during an incident.

# Single most important query: live utilization vs limit
sqlplus -S / as sysdba <<'SQL'
SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ('sessions', 'processes');
SQL

# Active vs total user sessions, total processes
sqlplus -S / as sysdba <<'SQL'
SELECT COUNT(*) AS active_sessions
FROM V$SESSION WHERE STATUS = 'ACTIVE' AND TYPE = 'USER';
SELECT COUNT(*) AS total_sessions FROM V$SESSION;
SELECT COUNT(*) AS total_processes FROM V$PROCESS;
SQL

# Group sessions by application/module/machine to find the heavy consumer
sqlplus -S / as sysdba <<'SQL'
SELECT NVL(MODULE, NVL(PROGRAM, 'unknown')) AS what,
       USERNAME, MACHINE, COUNT(*) AS sessions
FROM V$SESSION WHERE TYPE = 'USER'
GROUP BY NVL(MODULE, NVL(PROGRAM, 'unknown')), USERNAME, MACHINE
ORDER BY sessions DESC FETCH FIRST 20 ROWS ONLY;
SQL

# Listener state and whether services are BLOCKED
lsnrctl status

# Tail the alert log for ORA-00018 / ORA-00020 / handler refusal / ORA-27xxx
adrci exec="show alert -tail 100"

# OS-level: is the kernel capping us before Oracle does?
ulimit -u
cat /proc/sys/kernel/pid_max
ps -ef | grep -c ora_

When normal SYSDBA logon hangs because session/process state setup cannot acquire latches or mutexes, sqlplus -prelim starts a server process but skips full session/process state setup in the SGA. It may therefore help with a hang, but do not treat it as a bypass for a fully saturated PROCESSES limit:

# Preliminary connection when no slots are available
sqlplus -prelim / as sysdba

From there you can inspect the instance and identify zombies. As a last resort you can SHUTDOWN ABORT to recover, then STARTUP with a higher PROCESSES. SHUTDOWN ABORT is disruptive: every uncommitted transaction is rolled back via instance recovery on the next STARTUP. Reserve it for when no slot can be freed any other way.

How to diagnose it

1. Confirm you are at the limit. Check V$RESOURCE_LIMIT for ‘processes’ and ‘sessions’. CURRENT_UTILIZATION near LIMIT_VALUE confirms it live. MAX_UTILIZATION equal to LIMIT_VALUE confirms it happened in the past even if current is low.

2. Separate user sessions from background. Compare V$PROCESS row count against V$SESSION where TYPE=‘USER’. Background processes are non-negotiable; the rest is application-driven.

3. Find the consumer. Group V$SESSION by MODULE, PROGRAM, MACHINE, USERNAME. The top consumer is almost always one of: a single app tier with an oversized pool, a batch or scheduler module, or an ETL/reporting connection. Compare the session count to that app’s expected pool size.

4. Check for a connection leak. Look for sessions with old LOGON_TIME and INACTIVE status, especially from modules that should be cycling connections. A leak shows up as a count that grows over hours and never drops when the app is idle.

5. Rule out a lock contention cascade. If sessions are queuing behind a blocker, the pool grows to absorb the wait. Query V$SESSION for EVENT LIKE ’enq: TX%’ and inspect BLOCKING_SESSION. Killing the idle blocker often releases the pressure on PROCESSES within seconds. See the related blocking-sessions guide for the chain-walking query.

6. Rule out OS-level limits. If CURRENT_UTILIZATION is well below LIMIT_VALUE but new connections still fail, check ulimit -u for the oracle user, /proc/sys/kernel/pid_max, and the actual OS process count. The kernel can cap you before Oracle does.

7. Confirm parameter derivation. SESSIONS should be larger than PROCESSES. If SESSIONS was explicitly set to a value lower than the derived default, Oracle uses the derived default as its minimum and ignores lower values, so lowering SESSIONS below that threshold has no effect. Recheck TRANSACTIONS as well: its documented default is 1.1 * SESSIONS; whether it constrains an operation depends on undo management and the release.

Metrics and signals to monitor

Fixes

Right-size PROCESSES (requires restart)

If you have genuinely outgrown the configured limit, the only durable fix is to raise PROCESSES. Estimate the new value by taking peak MAX_UTILIZATION, adding headroom (peak should sit at or below 75% of PROCESSES), and rounding up. Apply in a maintenance window.

# Check current values
sqlplus -S / as sysdba <<'SQL'
SELECT NAME, VALUE, ISDEFAULT FROM V$PARAMETER
WHERE NAME IN ('processes','sessions','transactions');
SQL

PROCESSES is static. Changing it requires scope=spfile and a restart:

# Static change - requires restart. Plan a maintenance window.
sqlplus / as sysdba <<'SQL'
ALTER SYSTEM SET processes = <new_value> SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
SQL

SESSIONS auto-derives from the new PROCESSES value at restart. If you had set SESSIONS explicitly, remove the explicit setting so the default formula applies. In a PDB on 23ai, SESSIONS can be changed with ALTER SYSTEM at PDB scope without bouncing the CDB, but the CDB-level PROCESSES ceiling still applies. If TRANSACTIONS was set explicitly, recheck it; it defaults to 1.1 * SESSIONS.

Kill leaked or zombie sessions

If CURRENT_UTILIZATION is high but MAX_UTILIZATION has headroom and the legitimate load is low, the problem is leaked or zombie sessions. Identify them, then kill at the Oracle level first.

# Long-idle sessions from any module/machine
sqlplus -S / as sysdba <<'SQL'
SELECT SID, SERIAL#, USERNAME, STATUS, LOGON_TIME, MODULE, MACHINE
FROM V$SESSION
WHERE TYPE = 'USER' AND STATUS = 'INACTIVE'
  AND LOGON_TIME < SYSDATE - 1
ORDER BY LOGON_TIME;
SQL

# Kill a specific session (Oracle-level cleanup)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

If PMON cannot clean up (the process is stuck at the OS level), kill the OS process. This is more disruptive and must be targeted to a specific SPID:

# Map Oracle session to OS process
sqlplus -S / as sysdba <<'SQL'
SELECT s.SID, s.SERIAL#, p.SPID, s.MODULE
FROM V$SESSION s JOIN V$PROCESS p ON s.PADDR = p.ADDR
WHERE s.TYPE = 'USER' AND s.STATUS = 'KILLED';
SQL

# Destructive - targeted SPIDs only. Killing the wrong oracle process can crash the instance.
kill -9 <spid>
# Windows: ORAKILL <ORACLE_SID> <spid>

Fix the connection pool

The most common root cause is summed pool maxima that exceed what the database can serve. Sum the max pool size across every application instance, every service, and every batch worker. Add the background process count and DBA headroom. That sum is what PROCESSES must be sized to deliver.

Oracle’s Real-World Performance group recommends static connection pools based on physical CPU cores, with an official range of 1-10 connections per core; the ideal number is workload- and hardware-specific. The same guidance recommends an average of no more than 10 processes per CPU core on the database host. Dynamic pools that grow on demand are a frequent cause of connection storms: when latency rises, every application thread opens new connections simultaneously, the limit is hit, retries amplify the load, and the system thrashes.

The durable fixes live at the application tier:

• Cap every pool’s max size. Then sum them.
• Prefer static pool sizing over grow-on-demand.
• Configure connection validation so dead connections are evicted before they leak.
• Set SQLNET.EXPIRE_TIME for dead connection detection so firewalls and idle clients do not leave dangling slots.

Free slots under fire (incident only)

When the database is refusing connections and you cannot restart, kill leaked sessions to free slots. If normal SYSDBA logon hangs, try sqlplus -prelim; it still starts a server process but skips full session/process state setup in the SGA. From there you can run diagnostic commands, identify zombies, and as a last resort SHUTDOWN ABORT and restart with a higher PROCESSES. See the warning above about SHUTDOWN ABORT and instance recovery.

Prevention

• Track MAX_UTILIZATION daily. If it trends upward by N sessions per week, project when it hits 75% of PROCESSES. Plan the resize before the wall.
• Treat 75% of PROCESSES at peak as the soft cap. The remaining 25% is headroom for background processes, DBA sessions during incidents, and unexpected spikes.
• Sum every application’s pool maxima into a capacity plan. New service onboarded? Recompute.
• Cap dynamic pools. Static is safer.
• Monitor V$RESOURCE_LIMIT as a rate, not just a snapshot. A sudden jump in CURRENT_UTILIZATION is the early warning.
• Watch for INACTIVE sessions with old LOGON_TIME. They are leaks.
• Document any explicit SESSIONS or TRANSACTIONS values, and recheck them after every PROCESSES change.

How Netdata helps

• Per-second collection of V$RESOURCE_LIMIT CURRENT_UTILIZATION and MAX_UTILIZATION for both ‘processes’ and ‘sessions’, so a brief spike that touches the limit is visible even when a 1-minute poll would miss it.
• ML anomaly detection on the current utilization trend catches leaks and slow upward drift weeks before they hit the wall.
• Correlation of session count with listener BLOCKED status, alert log ORA-00020 / ORA-00018 / ORA-27xxx entries, and application-side pool errors shortens the diagnostic from “is it the database, the network, or the app?” to a single timeline.
• Alerting on MAX_UTILIZATION reaching LIMIT_VALUE tells you the limit was already hit even when current utilization has dropped, instead of discovering it during the next outage.
• Trend views of MAX_UTILIZATION over weeks give you the runway estimate (sessions per week to the limit) without manual spreadsheet work.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• Oracle blocking sessions: finding the blocker at the head of the chain
• Oracle ‘buffer busy waits’: hot blocks, sequence headers, and index leaf splits
• Oracle buffer cache hit ratio: the most misused metric in Oracle monitoring
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ‘cursor: pin S wait on X’: mutex contention on hot cursors
• Oracle ‘db file scattered read’: multiblock reads, full scans, and plan regressions
• Oracle ‘db file sequential read’: single-block index reads and buffer cache misses
• Oracle ’enq: TM - contention’: unindexed foreign keys and table-level locks