Blog

Learnomate Technologies > Blog > Oracle DBA > ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded

  • November 10, 2021
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments

ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.

Action: Increase the value of the SESSIONS initialization parameter.

ORA-00018 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

How to increase SESSION initialization paramete

1. Login as sysdba

sqlplus / as sysdba

2. Check Current Setting of Parameters

sql> show parameter sessions

sql> show parameter processes

 sql> show parameter transactions

3. If you are planning to increase "sessions" parameter you should also plan to increase "processes and "transactions" parameters.

processes=x

sessions=x*1.1+5

transactions=sessions*1.1

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

 sql> alter system set processes=500 scope=spfile;

sql> alter system set sessions=555 scope=spfile;

sql> alter system set transactions=610 scope=spfile;

sql> shutdown abort

 sql> startup
ORA-00018