Thursday, April 21, 2011

ORA-00018, ORA-00020 maximum number of sessions exceeded

Error Description
Any operation or a new session creation is failed. Like,
ORA-00018: maximum number of sessions exceeded

ORA-00020: maximum number of processes (string) exceeded

In order to solve the problem you have to increase the value of the PROCESSES/ SESSIONS initialization parameter.

You can see the current settings of the SESSIONS and PROCESSES parameter value by querying from v$spparameter or by simply show parameter parameter_name.

SQL> select name, value from v$spparameter where name in ('processes','sessions');

------------------------------ ----------
processes 150


To see the no. of   PROCESSES  &   SESSIONS use command:

SQL> show   parameter  session;
SQL>  show   parameter   process;

The explicitly value of the sessions parameter is not set and so it is left to blank. It's default value is derived from the processes parameter and value=1.1*process_parameter_value+5
You can calculate the default value of sessions parameter by,

SQL> select 1.1*value+5 "sessions par default value" from v$spparameter where name='processes';

sessions par default value

In order to change the value of the sessions you have to change it in spfile or pfile. Dynamically it can't be changed.

How to Solve the Problem

If your database start with spfile then,
Alter system set sessions=200 scope=spfile;
or simply you can consider to increase processes parameter by,
Alter system set processes=200 scope=spfile;
shutdown immediate;

If your database start with pfile then,
Open the pfile with an editor and edit the sessions parameter value and restart your database.

Similarly you can set the processes parameter.


Post a Comment

Auto Scroll Stop Scroll