ORA-00020: maximum number of processes exceeded

ORA-00020: maximum number of processes (%s) exceeded

If we got this error in alert log file we increased the number of process parameter, Oracle default value of process is 300. we can increase the process as per requirement when we setup the database.

Once this error generated then we are not able to login in the database. For increase the value of Process we follow following steps:

1. Connected to database with this prelim option.

sqlplus -prelim "/as sysdba"
Shutdown abort;
---Start the database

2. Verify the maximum value of resources utilization from following query.
Note: It will help to analyzed how maximum utilization is going.

select * from V$RESOURCE_LIMIT;

3. Check the values of parameters process,session,transaction.

sql> show parameter sessions
sql> show parameter processes
sql> show parameter transactions

Note: Oracle use following formula for the setting parameters:

4. Increase the value of process parameter with following command.

alter system set processes=500 scope=spfile;

Shutdown and startup the database
shutdown immediate

Note: Check the value of parameters again, it will automatically increase the value of other parameter according to above formula.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.