Share |
Also see following error messages:
EXP-00020: failed to allocate memory of size %lu

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.
 Related Oracle Errors  ORA-00444: background process "%s" failed while starting
ORA-00450: background process '%s' did not start
ORA-01092: ORACLE instance terminated. Disconnection forced
 Related Oracle Parameters 
user processes
maximum number of non-system user sessions allowed
maximum number of named users that can be created in the database
 Also check-out following related articles 
 Was this information helpful?  Yes No
If it was not helpful, please take some time to explain why. This is not to ask questions, you can do so in the forum.

Welcome to our forum for Oracle error: ORA-00020 Add your own message



What is the max. number of process parameter which can be declared for oracle 9i? At the parameter 350 i get the oracle error. So what is the maximum value or preferred value so that the error doesnt come again.
It all depends on how many sessions you concurrently expect and on how big the load is your machine can handle.
Every process takes some resources (memory, handles, ...) so of you only have 1Gb of memory, the number of processes handled will be lower than if you have 4Gb of memory for instance.

If you have a lot of simultaneous connections, all for a short period of time, you might want to consider configuring MTS (Multi-threaded server)

 
Oracle Error - 20 [ORA-00020 - Maximum number of processes (%s) exceeded.]

We are facing this problem.

Let me know what needs to be done to resolve this issue.
You need to increate the PROCESSES parameter

 

WIith an Oracel 9i database, running on a Windows 2003 server with 8 GB Ram, how do I determine what I can increase it to to avoid the error.?  It is current set at 250.

It depends if you are running 32-bit or 64-bit.

On 32-bit, the size of a data segment is maximum around 1,7Gb.

If your sga is already sized large, you will not be able to set the processes parameter to a very high number. processes are threads on Windows, and typically take up around 1MB memory.

So if your sga is set to 1Gb, you will be able to run 500 processes without any problem

 

ora-00020 (max no.of processes exceeds)

Warning:: you are no longer to connected to oracle

 

I am looking for a sql script to monitor the usage levels of sessions, processes and transactions in a db instance. If the usage exceeds a certain threshold, I will raise an alert.

I have come up with the below sql for "sessions" on a rac instance

 select s.inst_id, s.sessions_in_use from gv$parameter p, (select count(*) as sessions_in_use, inst_id
from gv$session group by inst_id order by inst_id) s
where p.inst_id = s.inst_id
and p.name = 'sessions'
order by inst_id

 

It would be highly helpful, if anyone can let me know if the above query would do or do we need something else.

 

thanks,

Rajesh

 

 

That's one way to do it. 

You could also incorporate the gv$resource_limit view:

select *from gv$resource_limit

(Check for resource_name processes, sessions, ...)

Enter your message : thanks Dbmotive...I will look into gv$resource_limit view as well. I will get back to you incase of any further queries.

Thanks,

Rajesh

Add your message

 Please provide your personal info 



 Please ask your message as briefly and clear as possible 

 Spam Protection 
Validation Code: 3hht0w65sts3evejv


Ask Your Question

If you need more information about this particular error message, you can leave a forum message.

We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.

Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.


Spam Protection

In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.

If you do not enter the validation correctly, your message will not be recorded.


Forum Rules

Please be polite, do not USE ALL UPPERCASE, no insults, violance or any other threats.