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

This is a very well known problem and it appears in alert log of the database as:

ORA-00020: maximum NUMBER OF processes (200) exceeded
 ORA-20 errors will NOT be written TO the alert LOG FOR
 the next MINUTE. Please look AT trace files TO see ALL
 the ORA-20 errors.

The resolution is even simpler by increasing static initialization parameter PROCESSES. The annoying part of this error is the warning that often directly comes from your users. Even if you monitor (and you should !!) all the ORA- error messages in alert log file you users might be faster than you to see it (obviously they are directly impacted). And even if you see it before them as it is a static parameters there is nothing you can transparently do in the background.

You are also aware that this maximum number of processes does not include only the sessions processes but also the background processes (Database writers, log writer, …) as well as parallel processes. So be cautious on the value of PARALLEL_MAX_SERVERS, even the default value can be huge on a small system.

So how could you be more proactive on this subject ?

Continue reading