Wednesday, March 18, 2015

ORA-00020: maximum number of processes exceeded

You may face this error message while connecting to the database instance which will also hinder you connecting with the database instance. The reason of this error is the value of init parameter “processes” which requires to be increased to allow Oracle instance to span a new process for new session request. For example, if value of this parameter is set to 100, it means that Oracle instance can only run total 100 processes including background processes and session processes. Once 100 processes threshold reaches, you will no longer be able to connect with the database.

Please note that this is not a dynamic parameter and an instance restart is required to take the new value in effect.

You may increase the value of this parameter using one of the following methods.
1.      If you are using pfile, edit value of parameter “processes” in the pfile according to your requirement and then do a database restart.
2.      If you are using spfile, issue “ALTER SYSTEM SET processes=500 scope=spfile” command (value 500 is just for demonstration, change it to match your requirement), and then do an instance restart.

If you are facing an issue whereby number of processes/sessions increasing very fast and keepincreasing, you may be hitting a problem similar to explained here.

No comments: