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

I was just about to test how oracle manage the processes parameter if we set the bar too low for processes value

processes = (1.5*sessions)+22

This is a test instance where i purposefully set the process value too low as 5

SQL> alter system set processes=5 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size            1157631712 bytes
Database Buffers          402653184 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

I bounce the database and check the value of processes and oracle automatically adjusted the value to high to 40

SQL> select name,value from v$parameter where name='processes';

NAME                 VALUE
-------------------- ---------------
processes            40

The number of processes touched the surface with value 38

[oracle@orcl19x ~]$ ps -ef|grep 'ora'|wc -l

Some of the non mandatory background processes were spawned and not able to run

2021-05-14 13:45:25.526000 +05:30
ORA-00020: maximum number of processes (40) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process Q001 submission failed with error = 20
Process QM03 submission failed with error = 20

When multiple sessions connected and the server process keeps spawn, process 41 not able to connect

C:\Windows\system32>sqlplus test/password@orcl19x

SQL*Plus: Release - Production on Fri May 14 14:31:27 2021

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

ORA-12537: TNS:connection closed

Enter user-name:

Number of processes at current moment

SQL> select count(*) from v$process;


Resource limit has max utilization of 40 which caused this error

SQL> col LIMIT_VALUE format a20
SQL> set lines 200
SQL> set pages 1000
SQL> select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('sessions','processes');

------------------------------ ------------------- --------------- --------------------
processes                                       39              40         40
sessions                                        49              55         82

From this,we can conclude that oracle automatically increase the process with flexibility according to the number background processes initially spawned

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