ORA-00020: maximum number of processes 150 exceeded

If any one getting below error in database alert log file. In this case we need to increase the process in parameter file(pfile or spfile). 

Alert log:-

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. 

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 this 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;