Script for Max process Reached issue: ORA-00020 maximum number of processes exceeded

Sometimes we got below error in our Oracle Database.


ORA-00020 maximum number of processes exceeded

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter


Now lets check what is the count of process and sessions our database has able to handle from the below query.

Do Remember-

If we are increasing the process parameter then we have to increase the memory as well because more number of process means increase in sessions and increase in sessions means demand of memory to get hold of session information during transaction.


Below queries helping to identify the current status of process & sessions in use and also it will help the status of all those sessions in HTML format.

****************************************************
$ sqlplus / as sysdba
set markup html on
spool session_details.html
select * from v$resource_limit;
select count(*) from v$session;
select server, count(*) from v$session group by server;
select status, count(*) from v$session group by status;
select * from v$session order by server, status;
spool off
exit
>>> Kindly upload <session_details.html>


$sqlplus / as sysdba

set markup html on
set pagesize 30
spool processes_sessions.html

select
p.username "V$PROCESS - OS USERNAME",
p.terminal,
p.program,
s.username "V$SESSION - USERNAME",
s.command,
s.status,
s.server,
s.process,
s.machine,
s.port,
s.terminal,
s.program
FROM v$session s,v$process p
WHERE p.addr=s.paddr
order by p.background desc;

spool off
exit;
*****************************************************


 If want to increase the process count hen follow below steps
++++++++++++++++++++++++++++++++++++++++++++++


How to increase PROCESSES initialization parameter:

1.    Login as sysdba
    sqlplus / as sysdba
  
2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions

3.    If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
    A basic formula for determining  these parameter values is as follows:
  
        processes=x
        sessions=x*1.1+5
        transactions=sessions*1.1
      
4.    These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
    sql> alter system set processes=500 scope=spfile;
    sql> alter system set sessions=555 scope=spfile;
    sql> alter system set transactions=610 scope=spfile;
    sql> shutdown abort
    sql> startup