oracle-error.jpg

Max number of processes exceeded

Thursday, October 20, 2016

What to do when max number of processes is exceeded in your Oracle database?


What do you do when the maximum number of processes is exceeded and you can not log on to the database?
Ah, that is a simple one! Just update your processes parameter or kill some old/unused sessions ...
Ah, that is a problem! Since max number of processes is reached, you can not log on to the database to make these changes ... 
That's hard!

 

This kind of error is listed in de log file of the listener as 
ora-12519, ora-12516, ...

Listener is ok and working.

But you can not create new sessions because the maximum number of processes is reached.


Solution:

Without having to kill operating system processes and restart the instance, which is a brut-force method.


Use sqlplus with the "preliminary connection"


sqlplus -prelim "/ as sysdba"


 SQL> shutdown abort
 ORACLE instance shut down.
 SQL> exit
 Disconnected from ORACLE

Now you can update your processes parameter if you like


sqlplus / as sysdba
 
 SQL> startup open

 alter system set processes=1000 scope=spfile;

 

That's solved!

/GW