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.