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





















































Comments








Post a Comment
























Popular posts from this blog









ORA-44412: XE edition memory parameter invalid or not specified






-















  SQL> connect sys as sysdba    Enter password:   Connected to an idle instance.     SQL> startup   ORA-44412: XE edition memory parameter invalid or not specified     SQL> startup pfile=C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts\init.ora   ORACLE instance started.     Total System Global Area  805306368 bytes   Fixed Size                  1289996 bytes   Variable Size             209715444 bytes   Database Buffers          591396864 bytes   Redo Buffers                2904064 bytes   Database mounted.   Database opened.    SQL> create spfile from pfile='C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts\init.ora';     File created.     SQL> shutdown   Database closed.   Database dismounted.   ORACLE instance shut down.    SQL> startup   ORACLE instance started.     Total System Global Area  805306368 bytes   Fixed Size                  1289996 bytes   Variable Size             209715444 bytes   Database Buffers          591396864 byt...








Read more










How to mount a WD Book Live as NFS in OEL6U3






-















  Scenario : I have a Windows 8 (Host) laptop on which I am running two OEL6U3 (Guest) machines in a Virtual Box. I want to have a shared mount between the two Linux machine which will allow me to read/write from both the machines concurrently. Its a very common scenario for a High Availability Environment.   Virtual Box provides a facility of creating sharable VDI disks which can be mounted on multiple machines, but it doesn’t allow concurrent read/write access from multiple machines.   If you are installing RAC database then its still achievable, because ASM will provide the feature of concurrent read/writes, but if you want to install a WebLogic cluster with High Availability the recommended approach would be to have a Shared Mount.   Step to create a shared nfs mount is fairly simple. I am using a WD My Book Live, and will mount the Public folder as shared mount   1. Get the hostname/ip of the WD device.   2. On the Linux Machine 1 create the folder where you want to mount the nfs ...








Read more










BPEL Engine Exceptions ORABPEL 02000 - 02179






-















BPEL Engine Exceptions ORABPEL 02000 - 02179 Engine Exceptions 02000 - 02179 ORABPEL-02000   severity:  10 type:  error name:  Block element class not valid description:  The class \"{0}\" for the block element \"{1}\" does not implement the ICubeBlock interface. All block elements contained within the process domain must implement this interface. ORABPEL-02001   severity:  10 type:  error name:  Error while creating block element description:  An error has occurred while attempting to instantiate the class \"{1}\" for the block element \"{0}\". The exception reported was: {2} ORABPEL-02002   severity:  10 type:  error name:  Unknown block element classname description:  The block element \"{0}\" is missing its classname; this element cannot be created without its classname. ORABPEL-02003   severity:  10 type:  error name:  Cube element missing id property description:  A Cube element is missing its \"id\" property. Every Cube...








Read more