ORA-00020: maximum number of processes (3000) exceeded
3 min readPROCESSES 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='*';
Hope it helped !! 🙂
