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> col name format a30
SQL> col value format a10
SQL> select name, value from v$spparameter where name in ('processes','sessions');
NAME VALUE
------------------------------ ----------
processes 150
sessions
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
--------------------------
170
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;
shutdown immediate;
startup;
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.
No comments:
Post a Comment