ORA-00020 maximum number of processes exceeded

mailtonivass

Cause: All process state objects are in use.

Simply increase your processes parameter and you are all set!

 The ORA-00020 is a serious production error because a user cannot connect. 

The ORA-00020 is caused by two things:  

  1. Disconnected processes:  Rogue “zombie” connections  to Oracle that are idle (not working).  To fix this, use the ALTER SYSTEM KILL command.  You may also need to kill session at the OS level with the KILL -9 or the ORAKILL command.  

  2. Too few process buckets:  Oracle limits the number of connected processes with the processes parameter, and you may get the ORA-00020 error as the natural result in growth of system usage.

Solution:

To fix this, increase the processes parameter, usually doubling the value to allow for future growth.

1.    Login as sysdba

              sqlplus / as sysdba

2. Check Current Setting of Parameters

sql> show parameter sessions
 sql> show parameter processes
sql> show parameter transaction

3. These paramters can’t be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instances.

sql> alter system set processes=500 scope=spfile;
sql> alter system set sessions=555 scope=spfile;
sql> alter system set transactions=610 scope=spfile;
sql> shutdown immediate
sql> startup

Leave a comment