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.
Reference: Oracle Documentation

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:
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 

Related Links:
- ORA-04031: unable to allocate n bytes of shared memory
- ORA-00054: resource busy and acquire with NOWAIT specified
- ORA-00020 maximum number of processes exceeded  


  1. Great upload...to the point. My install is running smooth now.

    Regards, Scott N.

    1. http://nimishgarg.blogspot.in/2012/05/ora-00020-maximum-number-of-processes.html might be useful to you

  2. Awesome! thanks for the solution !

  3. Hi,
    I want to know that the SESSION initialization parameter would be same as db connection pool for oracle database?
    what is the difference between SESSION initialization parameter and db connection pool in oracle database?

    1. Sessions initialization parameter says how many different sessions can be created by multiple clients at a single point of time.

      db connection pooling is a done at middle tier (application server) so that application can avoid the overhead to create and drop connections for each and every transaction. Application creates N connections to DB and put them in a connection pool and which can be reused when future requests to the database are required.


  4. Hi Nimish: A small query:- Is there a reason behind all the below calculations
    if Processes=x then Sessions = x * 1.1 + 5 and
    Transactions = Sessions * 1.1
    Or is it the ideal scenario....

    <<<< I have got another doubt (not related to this article): How the High Water Mark is useful in real time scenario.....If you could write an article on this too then it will be useful. >>>>