How to avoid ORA-00018 maximum number of sessions exceeded

How to avoid ORA-00018 maximum number of sessions exceeded

Oracle doc says

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login 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.

Number of sessions = (1.5 * PROCESSES) + 22

The total process and session limit on the database which says the number of users who can connect to the database and process which can be allocated to users

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     300

SQL> show parameter session

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sessions                             integer     472

Identify the total sessions currently connected to database currently

SQL> select count(1) from v$session;

  COUNT(1)
----------
        52

SQL> select count(1) from v$process;

  COUNT(1)
----------
        63

Total active sessions on the database

SQL> select sid,serial#,username from v$session where status='ACTIVE';

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        37      17176 SYS
        40      10985 SYS
        43      31501 SYS
        45      50043 SYS
        47      54359 SYS

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        67      59264 C##GGUSRS
        73      31623 C##GGUSRS
        74      19460 C##GGUSRS
         85       6987 SYS

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        89       9809 C##GGUSRS
        97      16161 C##GGUSRS
        99      25511 SYS

Identify the resource current and max utilized

SQL> select resource_name,current_utilization,max_utilization from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
processes                                       73              86
sessions                                        85             116

Calculate the load of the session history on database and allocate double the values of utilized sessions and processes parameter to avoid ORA-00018 maximum number of sessions exceeded.

Example: If my max utilization of processes are 500 ,it is always recommended to allocate 1000 for processes parameter likewise for sessions

SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     500

SQL> alter system set processes=1000 scope= spfile
System altered.