Oracle problems

ORA-00020: Maximum Number of Processes Exceeded. Who Took Them, and How to See It Coming

2 November 2026

ORA-00020 is the error that locks the DBA out too. The database runs, every process slot is taken, and the connection that would fix it cannot be made. Almost always, the slots went to connection pools that grew, or to sessions that never left.

What the error says

ORA-00020: maximum number of processes (300) exceeded. The instance has processes set to 300, and every slot is in use: server processes for sessions, background processes, parallel servers and job slaves. A new connection needs one and is refused. Its companion, ORA-00018: maximum number of sessions exceeded, is the same wall from the other side: sessions is derived from processes and usually hits later.

In a container database the limit is per instance, shared by all pluggable databases. One PDB with a leaking pool takes the slots of the others.

How close you are

SELECT r.inst_id, r.resource_name, r.current_utilization,
       r.max_utilization, TO_NUMBER(r.limit_value) AS limit_value,
       ROUND(100 * r.current_utilization / r.limit_value) AS pct_now,
       ROUND(100 * r.max_utilization / r.limit_value)     AS pct_peak
FROM   gv$resource_limit r
WHERE  r.resource_name IN ('processes', 'sessions')
ORDER  BY r.inst_id, r.resource_name;

MAX_UTILIZATION is the high-water mark since the instance started. A peak at 97% with the current value at 60% means you touched the wall once, usually when an application restarted and all its pools reconnected at the same moment.

Who holds them

SELECT inst_id, username, machine, program, module, status,
       COUNT(*) AS sessions,
       SUM(CASE WHEN status = 'INACTIVE' AND last_call_et > 3600
                THEN 1 ELSE 0 END) AS idle_over_1h
FROM   gv$session
WHERE  type = 'USER'
GROUP  BY inst_id, username, machine, program, module, status
ORDER  BY sessions DESC
FETCH FIRST 20 ROWS ONLY;

The shape of the answer names the cause:

One machine with hundreds of INACTIVE sessions, idle for hours. A connection pool with a large maximum and no idle timeout. Multiply the pool maximum by the number of application servers: that is what the database must hold after every restart.
Sessions that grow by a few an hour and never fall. A leak, usually code that opens a connection on an error path and never closes it.
Hundreds of sessions from one program, created in the same minute. A logon storm. Pools reconnect all at once after a network blip or a failover.

Processes without a session. Clients that died and left their server process behind:

SELECT inst_id, COUNT(*) AS processes_without_session
FROM   gv$process p
WHERE  p.background IS NULL
AND    p.pname IS NULL
AND    NOT EXISTS (SELECT 1 FROM gv$session s
                   WHERE s.inst_id = p.inst_id AND s.paddr = p.addr)
GROUP  BY inst_id;

SQLNET.EXPIRE_TIME = 10 in the sqlnet.ora of the server lets Oracle probe the client every 10 minutes and free the process when nobody answers.

Get in when nobody can

On the database server, as the Oracle owner. A local connection needs a process slot too, so it may fail as well. sqlplus -prelim / AS SYSDBA attaches to the instance without a slot, but it can only shut it down. Keep that for last.
Free a few slots at the operating system. The dedicated server processes of idle sessions are named oracleORCL (LOCAL=NO). Kill two or three of them with kill -9, then connect normally. Only those: a background process killed this way takes the instance down with it.

Then clean up from inside. Generate the statements for the sessions of the pool that caused it:

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial#
       || ',@' || inst_id || ''' IMMEDIATE;' AS statement
FROM   gv$session
WHERE  type = 'USER'
AND    status = 'INACTIVE'
AND    last_call_et > 3600
AND    machine = 'app07';

Fix it for good

Size the pools to the database, not the other way round. The sum of all pool maximums must fit under processes with room for the background processes, the parallel servers, the jobs and the DBA. Twenty percent of headroom is a reasonable rule.
Raise processes with a restart. ALTER SYSTEM SET processes = 600 SCOPE=SPFILE;, then restart, one instance at a time in a RAC. sessions and transactions follow by themselves unless they were set explicitly; check ISDEFAULT in V$PARAMETER. Each process costs memory, so the number is not free.
Let Oracle drop idle and dead sessions. SQLNET.EXPIRE_TIME for dead clients. For idle sessions, ALTER SYSTEM SET max_idle_time = 60; in minutes, available from 12.2, or a resource manager plan with an idle limit.
Pool on the database side when many short-lived clients connect and disconnect: Database Resident Connection Pooling gives them a small set of server processes to share.

See it coming

The process count climbs for hours before ORA-00020, except in a logon storm, when it climbs in a minute. Radar Engine, the detection layer of Scops, covers both shapes:

Detector	Clock	When it fires
Processes near the limit	Hourly level and forecast, against `processes`	70%: Watch. 80%: Warning. 90%: Critical. Repeated peaks at 97%: Critical, at 90%: Warning. Trend against 2, 7 and 21 days.
Sessions near the limit	Hourly, against `sessions`	As for processes.
Processes running out	Live, every 5 minutes	The limit within 90 minutes: Warning. Within 20 minutes, or 90% used: Critical.
Session surge	Live, against the hour before	50%, or three times the usual and rising: Warning. 75% of the limit: Critical.
Logon storm	Live	5 logons a second or more, against the hour before: Warning. Five times the usual and 15 a second: Critical.
Connection leak from a module	Hourly	The sessions of one module rise for days: Watch. Session use at 60%: Warning.

The leak detector is the one that matters most, because a leak is invisible at any single reading: 180 sessions today, 190 tomorrow, each number normal on its own. The shape is the finding. With the OS credential of the database, the log detectors also report ORA-00020 from the alert log, and listener errors such as TNS-12516 count as Critical only when the database is near the process or session limit, which is when they mean this.

Questions

Does raising processes need a restart?
Yes. The parameter is static: ALTER SYSTEM SET processes with SCOPE=SPFILE, then a restart. In a RAC, one instance at a time.

Why do I get ORA-00020 when V$SESSION shows fewer sessions than processes?
Processes count more than sessions: background processes, parallel servers, job slaves and server processes whose client died. Compare the processes row of V$RESOURCE_LIMIT, not a count of V$SESSION.

Can I free processes without connecting?
On the server, you can kill dedicated server processes, the ones named with LOCAL=NO, at the operating system; the instance cleans up after them. Never a background process, that takes the instance down.

What is a safe value for processes?
There is no universal number. Add the pool maximums of every application, the background processes, parallel_max_servers and the job slaves, and leave about twenty percent of room. Each process costs memory, so do not set it to ten times that.

Sources

Oracle: ORA-00020, ORA-00018
Oracle: V$RESOURCE_LIMIT, PROCESSES, MAX_IDLE_TIME
Scops docs: Detector reference