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

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

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter.

ORA-00020 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

SOLUTION:

 

How to increase PROCESSES initialization parameter:

1. Login as sysdba

sqlplus / as sysdba

2. Check current setting of parameters

 

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

3. If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
A basic formula for determining these parameter values is as follows:

processes=x
sessions=x*1.1+5
transactions=sessions*1.1
 

4. These parameters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.

sql> alter system set processes=700 scope=spfile;
sql> alter system set sessions=775 scope=spfile;
sql> alter system set transactions=852 scope=spfile;
sql> shutdown abort
sql> startup

 

NOTE:

Sometimes you may come across situation where due to this error you may not able to connect to database so in that situation you have to follow below steps.

Error:

ERROR:

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

SP2-0306: Invalid option.

Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]] where <logon> ::=< username>[/<password>][@<connect_identifier>]

<proxy> ::=< proxyuser>[<username>][/<password>][@<connect_identifier>]

ERROR:

ORA-01005: null password given; logon denied

SOLUTION:

1. Login as sysdba using following command and shut down database.

sqlplus -prelim "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:09:15 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> shutdown abort

ORACLE instance shut down.
 

SQL> exit

isconnected from ORACLE

2. Start database and follow same procedure as above.
 

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:10:38 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2248080 bytes
Variable Size 1258291824 bytes
Database Buffers 855638016 bytes
Redo Buffers 21708800 bytes
Database mounted.
Databasen opened.

 

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!