ORA-00020: maximum number of processes exceeded

ORA-00020: maximum number of processes (N) exceeded

The instance reached its PROCESSES limit, so new connections are refused. Find out who is holding the connections, fix any connection leak, and raise PROCESSES only if the load is real.

What ORA-00020 means

Every dedicated server connection and every background process uses one slot of the PROCESSES initialization parameter. When all slots are taken, new logins fail with ORA-00020. Because even administrators may not be able to log in normally, this error often looks like a full outage although the database itself is healthy.

Common causes of ORA-00020

An application or connection pool leaking connections, or a pool sized larger than the database allows.
Many idle sessions left open by tools and users.
A slowdown: sessions pile up because each request takes longer, and the pool opens more connections.
PROCESSES set too low for the real workload.

How to fix ORA-00020

Check current and peak usage in V$RESOURCE_LIMIT.
Group V$SESSION by program, machine and user to see who holds the connections.
Fix the source: close leaking connections and size pools so that their total stays under the limit.
If the load is legitimate, raise the limit: ALTER SYSTEM SET processes = n SCOPE = SPFILE. It takes effect after a restart.

ORA-00020 example

How close are we, and who is connected?

SELECT resource_name, current_utilization, max_utilization, limit_value
  FROM v$resource_limit
 WHERE resource_name IN ('processes', 'sessions');

SELECT program, machine, username, COUNT(*) AS sessions
  FROM v$session
 GROUP BY program, machine, username
 ORDER BY sessions DESC;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-00020. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.