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: 1.5 * PROCESSES + 22 on 12c and later, 1.1 * PROCESSES + 5 on 11g and earlier. Background processes consume roughly 30 to 60 process slots before any user connects, so a PROCESSES value of 300 leaves somewhere around 240 to 270 slots for actual user sessions.

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. In Oracle 23ai, SESSIONS can be modified 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

CauseWhat it looks likeFirst thing to check
Connection pool misconfigurationMany app instances, each with an oversized max pool. Linear growth as you scale app tier.Sum max pool sizes across all app instances. Compare to PROCESSES minus background.
Connection leakCURRENT_UTILIZATION drifts upward over hours or days, never drops on idle.V$SESSION where LOGON_TIME is old and STATUS=INACTIVE.
Session storm from retry logicSudden spike. App retries on failure, amplifying load.Alert log around the ORA-00020 timestamp. Look for cascading app errors.
Lock contention cascadeSessions queue behind an idle blocker. Pools grow to absorb wait.V$SESSION.BLOCKING_SESSION and wait event enq: TX - row lock contention.
Runaway job schedulerCJQ0/Jnnn spawns many concurrent jobs. Spike correlates with job window.V$SESSION.PROGRAM or MODULE matching the scheduler.
OS limits hit firstOracle shows headroom but new processes still fail.ulimit -u, /proc/sys/kernel/pid_max, OS process count.

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_

If even SYSDBA cannot connect because the limit is fully saturated, sqlplus -prelim opens a preliminary connection that does not allocate a server process:

# 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 formula result, Oracle applies the derived value anyway, so lowering SESSIONS below the derived value has no effect. Recheck TRANSACTIONS as well: it defaults to 1.1 * SESSIONS and is a hidden second ceiling.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
V$RESOURCE_LIMIT CURRENT_UTILIZATION / LIMIT_VALUE for ‘processes’Live headroom against the hard cap.Sustained above 75% at peak; any sustained reading above 85%.
MAX_UTILIZATION for ‘processes’ and ‘sessions’Records whether the limit was hit since startup.MAX_UTILIZATION equal to LIMIT_VALUE means connections were already refused.
Listener registered services statusBLOCKED services mean the instance has no free handler.BLOCKED status in lsnrctl output or TNS-12516/TNS-12519 in client logs.
Active vs total user sessionsDistinguishes active work from idle or leaked sessions.Total growing while active stays flat signals a leak.
Sessions per application modulePinpoints the consumer burning slots.One module’s count trending upward over time.
OS process count, ulimit -u, pid_maxThe kernel may refuse forks before Oracle hits PROCESSES.OS process count near ulimit -u.
Alert log for ORA-00018, ORA-00020, ORA-27xxxInstance-level confirmation of refusals.Any new occurrence.
Application pool-exhaustion errorsFirst downstream sign of an Oracle-side cap.Pool timeouts, retries, ORA-03113/ORA-03135.

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 sized at a maximum of roughly 5 connections per CPU core, and 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 SYSDBA cannot connect, use sqlplus -prelim, which does not allocate a server process. From there you can inspect V$RESOURCE_LIMIT and V$SESSION, 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.