EXP-00020: failed to allocate memory of size %lu
ORA-00020: Maximum number of processes (%s) exceeded
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) exceededWhen 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: 3When you check your spfile, you see that one of the following is set to 0:
*.license_max_sessions=0 *.license_max_users=0You 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
Remove following entries if they exist:
*.license_max_sessions=0 *.license_max_users=0Next, 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.
Welcome to our forum for Oracle error: ORA-00020 Add your own message
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)
We are facing this problem.
Let me know what needs to be done to resolve this issue.
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
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.
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.
