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’;
VALUE
——————————————————————————–
150
or
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:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1
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
SQL>startup
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.
Thank you for this informative blog. We offer Web Development Services, Mobile App Development, Software Development, Internet Of Things and Digital Marketing. Your website presents your company's status and character — Invictus Solutions will help you improve your image.