LLI Wiki — My Digital Knowledge Base

Oracle ORA-00020: maximum number of processes exceeded

Use sqlplus -prelim to shut down an instance saturated with processes, then adjust the processes parameter.

The error ORA-00020: maximum number of processes (n) exceeded appears when the processes parameter is reached: no new session can connect, including sqlplus / as sysdba. This page describes how to regain control of the instance and adjust the parameter.

Check process usage

If a connection is still possible:

select resource_name, current_utilization, max_utilization
from v$resource_limit
where resource_name in ('processes','sessions');
show parameter processes

Connect with -prelim

The -prelim option opens a preliminary connection that does not create session structures in the SGA. It lets you shut down the instance even when the processes are exhausted:

sqlplus -prelim "/ as sysdba"
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit

Restart the instance

sqlplus / as sysdba
SQL> startup

Adjust the processes parameter

Choose a value suited to the workload, then restart the instance for it to apply (scope=spfile):

alter system set processes=500 scope=spfile;

The lines processes=30, processes=100 and processes=500 are examples: run only one of them.

Check the value after the restart:

show parameter processes
SHOW PARAMETER SESSION;

Last resort: kill the operating system processes

On Linux, the source author killed all the instance processes before restarting. This method is brutal and not recommended:

for a in $(ps -ef | grep $ORACLE_SID | grep -v grep | awk '{ print $2}'); do
 kill -9 $a;
 done ;

Notes

Mastodon LLIMastodon SocialFosstodon