eHealth system log shows ORA-00018 maximum number of sessions exceeded

Document ID : KB000031327
Last Modified Date : 14/02/2018
Show Technical Document Details

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 messages are generated by the Oracle database server when running any Oracle program.

SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.

A single connection can have 0, 1, or more sessions attached to it.

How to increase SESSION initialization parameter

  1. Stop eHealth services
    nhServer stop

  2. From command line, login as sysdba

    Oracle 10:
    sqlplus "sys/ehealth as sysdba"

    Oracle 11/Windows:
    sqlplus "sys/ehealth@%NH_DB_CONNECT_STRING% as sysdba"

    Oracle 11/Solaris or Linux:
    sqlplus "sys/ehealth@$NH_DB_CONNECT_STRING as sysdba"

  3. Check Current Setting of Parameters

    show parameter sessions
    show parameter processes
    show parameter transactions

  4. If you are planning to increase "sessions" parameter you should also plan to increase the 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  

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

    alter system set processes=500 scope=spfile;
    alter system set sessions=555 scope=spfile;
    alter system set transactions=610 scope=spfile;
    exit

  6. Stop the database, then restart it and eHealth.

    nhStopDb immediate
    nhStartDb
    nhServer start