If any one 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.
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.