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
startup

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;
select * from DBA_HIST_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:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1

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

alter system set processes=500 scope=spfile;

Shutdown and startup the database
shutdown immediate
startup

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

Advertisements

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.