Site icon IT Tutorial

ORA-00020: maximum number of processes (%s) exceeded

How to increase PROCESSES initialization parameter : 

Check Current Setting of Parameters

1)show parameter sessions

2)show parameter processes

3)show parameter transactions

 

A basic formula for determining  these parameter values is as follows below:

 processes=x

 sessions=x*1.1+5

 transactions=sessions*1.1

 

These paramters can’t be modified in memory. You have to modify the spfile only (scope=spfile) and close the instance.

SQL> alter system set processes = 170 scope = spfile;

SQL> alter system set sessions = 342 scope = spfile;

SQL> alter system set transactions = 376 scope = spfile;

We need to close the database:

SQL> shutdown immediate;

SQL> startup;

Check parameter values :

SQL> select name, value
  2  from v$parameter
  3  where name in ('processes', 'sessions', 'transactions');

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
processes
170

sessions
342

transactions
376

Then completed



 

Exit mobile version