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
- Killing Oracle processes with
kill -9can leave the instance in an inconsistent state: prefershutdown abortthrough-prelim. - Reference on
-prelim: author's blog.