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



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  
 


	  
      
























Labels:














9 comments:








  1. ScottNFebruary 25, 2012 at 2:39 AM
    Great upload...to the point.  My install is running smooth now.

    Regards, Scott N.
    ReplyDelete
    Replies
    1. Nimish GargMay 14, 2012 at 1:06 PM
      http://nimishgarg.blogspot.in/2012/05/ora-00020-maximum-number-of-processes.html might be useful to you
      Delete
  2. AnonymousMay 24, 2013 at 6:17 PM
    Awesome! thanks for the solution !
    ReplyDelete
  3. UnknownOctober 16, 2013 at 3:55 PM
    Hi,
    I want to know that the SESSION initialization parameter would be same as db connection pool for oracle database?
    OR
    what is the difference between SESSION initialization parameter and db connection pool in oracle database?
    ReplyDelete
    Replies
    1. Nimish GargOctober 16, 2013 at 6:44 PM
      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.

      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:22140261281764
      Delete
    2. AnonymousApril 28, 2014 at 2:52 PM
      great
      Delete
  4. UnknownDecember 5, 2013 at 12:41 PM
    Can i change this online?
    ReplyDelete
  5. SKNMarch 2, 2016 at 6:19 AM
    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. >>>>
    ReplyDelete




























        

      










Subscribe to:
Post Comments (Atom)