Knowledge Base

You have reached the maximum number of processes and are unable to logon to you Oracle database

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

You have reached the maximum number of processes the database is allowed to create.

Every dedicated connection attempt will create a new thread (Windows) or startup a new process (Unix).
This can have an impact on the server, especially when resources are sparse.
In order to minimize the risk of exhausted resources, you can specify the PROCESSES parameter in your pfile or spfile.

In order to be able to make new connections, you can choose to close unneeded connections, or increase the PROCESSES parameter.
If you choose to change the parameter, a database bounce (stop/start) is required.

When you receive this error at instance startup, or when connecting AS SYSDBA, you might have set license_max_sessions or license_max_users set to 0.

SQL> conn / as sysdba
ERROR:
ORA-00020: maximum number of processes (150) exceeded

When you look at the oradim.log file, you see following error when your service is not started:

C:\oracle\product\11.2.0\dbhome_1\bin\oradim.exe -startup -sid ora112 -usrpwd *  -log oradim.log -nocheck 0 
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00450: background process 'QMNC' did not start
ORA-00444: background process "QMNC" failed while starting
ORA-00020: maximum number of processes () exceeded
Process ID: 3964
Session ID: 125 Serial number: 3

When you check your spfile, you see that one of the following is set to 0:

*.license_max_sessions=0
*.license_max_users=0

You will need to remove the values from spfile.
First create a pfile:

SQL> create pfile from spfile;

File created.

Next, go to your %ORACLE_HOME%/database or $ORACLE_HOME/dbs directory and open init.ora
Remove following entries if they exist:

*.license_max_sessions=0
*.license_max_users=0

Next, recreate the spfile:

SQL> create spfile from pfile;

File created.

And startup the database:

SQL> startup mount;
ORACLE instance started.

Total System Global Area  167387136 bytes
Fixed Size                  1373320 bytes
Variable Size             109054840 bytes
Database Buffers           50331648 bytes
Redo Buffers                6627328 bytes
Database mounted.
SQL> alter database open;

Database altered.
Oracle Gold Partner DbMotive is an Oracle Gold Partner