Wednesday, February 20, 2013

ORA-00018 maximum number of sessions exceeded


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 parameter:
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.
 
A basic formula for determining  these parameter values is as follows:
 
  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 
 

No comments: