A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

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

3 min read
yellow scrabble tiles

Photo by Ann H on Pexels.com

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.

If you just want to know the maximum number of sessions allowed, then you can execute in sqlplus, as sysdba:

SQL> show parameter sessions
    NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     3000
shared_server_sessions               integer
select value from v$parameter where name = 'processes';

v$resource_limit is a very useful view for day-to-day work because you can glance in a moment if there is or were some process reaching his limit

select resource_name, current_utilization, max_utilization, limit_value 
    from v$resource_limit 
    where resource_name in ('sessions', 'processes');

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes                      -             -         -
sessions                      -             -         -

Typical ORA-errors

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

Troubleshoot

Identify which DB instance is having issue

select inst_id, resource_name, current_utilization, max_utilization from gv$resource_limit where resource_name='processes' order by inst_id;

Gather a count of the current connections

select machine,count(*) from v$session group by machine order by machine;
select count(*) from v$session; 
set lin 200 pages 200
col username for a30
select inst_id,username,count(*) from gv$session group by inst_id,username order by inst_id;

Gather Application level Processes with the greater than 50 connections.

COLUMN process FORMAT a10
SELECT inst_id, process, substr(machine,0,30) machine, substr(program,0,30) program, count(process) count
  FROM gv$session
 WHERE process != '1234'
HAVING count(process) > 50
 GROUP BY inst_id, process, machine, program
 ORDER BY count, machine;

Get a historical count of connections.

set linesize 280
set pagesize 1000
column END_INTERVAL_TIME format a30 truncate;
column RESOURCE_NAME format a15 truncate;
SELECT   rl.snap_id, s.end_interval_time, rl.resource_name, rl.current_utilization as sessions
FROM dba_hist_resource_limit rl, dba_hist_snapshot s
WHERE s.snap_id = rl.snap_id AND rl.instance_number=1 and rl.resource_name = 'sessions'
ORDER BY s.begin_interval_time, rl.instance_number;
with a as (
SELECT rl.snap_id, rl.instance_number, s.end_interval_time, rl.resource_name, rl.current_utilization as sessions
FROM dba_hist_resource_limit rl, dba_hist_snapshot s
WHERE s.snap_id = rl.snap_id and rl.resource_name = 'sessions'
--and end_interval_time >= SYSDATE -90
ORDER BY s.begin_interval_time, rl.instance_number),
 b as (
 select to_char(end_interval_time, 'MM/DD/YY') snap_date, instance_number, sessions
 FROM a )
select snap_date,  instance_number, MAX(sessions)
from b
group by snap_date,  instance_number
ORDER BY snap_date ,  instance_number;
 
SNAP_DAT INSTANCE_NUMBER MAX(SESSIONS)
-------- --------------- -------------

 I want to increase the max allowed number of sessions so I am going to increase the number of processes from 3000 to 6000.

alter system set processes=6000 scope=spfile sid='*';