ORA-00020: maximum number of processes(%s) exceeded

ORA-00020: maximum number of processes(%s) exceeded

What is the root cause of this ORA-00020 error and how we can fix it?

Answer  :  The ORA-00020 is a serious production database error because a user cannot connect.

In easy words, whatever processes limit we configure in the database for max process, the process usage already reached to that limit.

Important note : This max process limit is for a single session, not referring to all database accumulated processes from all the sessions.

Sometime the max processes can be hit due to some issue at the database or from the application, a session keep opening the new cursor without closing the existing cursor, it will also cause for the processes to grow.

If we find that the processes limit is really lower than what as our requirement then using the below fix can increase the max process limit for each session.

The fix is to find out which session having more processes and another way to fix it double the current value using the below command

sqlplus /nolog
connect sys as sysdba
alter system set processes=200 scope = spfile;
exit

To investigate the processes usage

--current process count
SELECT count(*) FROM v$process;


---utilization history
SELECT b.begin_interval_time,b.snap_id, a.current_utilization,a.max_utilization,a.initial_allocation
     FROM dba_hist_resource_limit a, sys.dba_hist_snapshot b
     WHERE a.resource_name='processes'
     AND a.snap_id=b.snap_id
     ORDER BY a.snap_id DESC;


---schema wise history of process count –need to pass the snap_id from above query which has the highest number of CURRENT_UTILIZATION.
SELECT parsing_schema_name,COUNT(*) FROM dba_hist_sqlstat WHERE snap_id=39914 GROUP BY parsing_schema_name;

Please describe your issue in the comment area, if we can help you.