ORA-00018: maximum number of sessions exceeded

You receive the following errors when trying to make new connections using SQL Developer or any other client:

ORA-00018: maximum number of sessions exceeded

Resolution:

Login as sysdba 

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 28 03:32:48 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> 

Check the resource limit

SQL> set lines 256

SQL> set trimout on

SQL> set tab off

SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION                       LIMIT_VALUE                                  CON_ID

------------------------------ ------------------- --------------- ---------------------------------------- ---------------------------------------- ----------

processes                                      110             120       2500                                     2500                                        0

sessions                                       130             142       3772                                     3772                                        0

Check the Session Parameters

SQL> show parameter session;




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

java_max_sessionspace_size           integer     0

java_soft_sessionspace_limit         integer     0

license_max_sessions                 integer     0

license_sessions_warning             integer     0

session_cached_cursors               integer     50

session_max_open_files               integer     10

sessions                             integer     3772

shared_server_sessions               integer

Check the current number of sessions.


SQL> Select count(*) from v$session;




  COUNT(*)

----------

       3772

If the count of current sessions is greater than or equal to sessions LIMIT_VALUE we need to either reduce the number of open sessions or increase the limit.

Option #1: Increase the value of the SESSIONS parameter in the parameter file.

SQL> alter system set sessions= scope=spfile;

SQL> shutdown immediate

SQL> startup

Option #2: Increase the value of PROCESSES since SESSIONS is derived off of the PROCESSES parameter.

DB version prior to 11.2PROCESSES = (SESSIONS - 5)/1.1




DB version after 11.2
PROCESSES = (SESSIONS - 22)/1.5




SQL> alter system set processes= scope=spfile;

SQL> shutdown immediate

SQL> startup