Wednesday, February 23, 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> 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;
or simply you can consider to increase processes parameter by,
Alter system set processes=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.

Centos 7 reset root/ any user lost password / lockout due to cant remember password

1. Need to be in front of the terminal. (Physically if not vm). 2. Reboot the server 3. Press 'e' in the GRUB2 boot screen. 3. bunch...