How to fix ORA-00020 maximum number of processes 150 exceeded

If anyone getting below error ‘ORA-00020 maximum number’ in database alert log file. In this case we need to increase the process in parameter file (pfile or spfile). 

Alert log:- Below error will appear in database alert log file. You can simple go to trace location where alert log generating and check the error by using more( more alert_SID.log) , cat (alert_SID.log) or vi (vi alert_SID.log).

Error-:  ORA-00020 maximum number of processes 150 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.

To check the current configuration in database.  You can see blow our current process value is 150.
SQL>select value from v$parameter where name = ‘processes’;
SQL>show parameter process;
SQL>show parameter sessions;
SQL>show parameter transactions;
Note:- Before change “PROCESSES” parameter you should also plan to increase “sessions and “transactions” parameters.
Formula for determining  these parameter values is as follows:
if  process=500
    sessions=500*1.1+5  = 555
    transactions=sessions*1.1 = 610
To resolve ‘ORA-00020: maximum number of processes 150 exceeded’ need to be increase the processes value in oracle database. Do the same as below.
SQL> alter system set processes=500 scope=spfile;
System altered.
Once alter the processes value, need to be restart the oracle database to effect the changes.
SQL>shutdown immediate
In some situation we don’t want to bounce the database. To resolve the issue temporarily we need to kill inactive session.
SQL>select ‘kill -9 ‘ || p.SPID, s.USERNAME, ‘alter system kill session ”’||sid||’,’ || s.serial# || ”’;’,s.STATUS,s.logon_time,s.terminal,s.machine from v$session s, v$process p
where s.PADDR = p.ADDR (+) and s.STATUS=’INACTIVE’ order by 1;
Hope this post will helpful to resolve and identify the issue in you database.
If you are interest to read another post which is more useful to resolve the issue ‘ORA-14287: cannot REBUILD a partition’.

One more request to check the similar post that is related to ORA-00020.

1 thought on “How to fix ORA-00020 maximum number of processes 150 exceeded”

Leave a Comment