ORA-00018 maximum number of sessions exceeded

 Error:

ORA-00018 maximum number of sessions exceeded

SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00018: maximum number of sessions exceeded

Cause:

Maximum number of session parameter value is reached.

Solution:

Increase the value of Session parameter in parameter files.

1. Check the resource limit view for maximum session reached value.

It show the current and maximum utilization of Sessions parameter.

SELECT * FROM v$resource_limit WHERE resource_name = 'SESSIONS';

RESOURCE_NAME     CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
----------------- ------------------- --------------- ---------- ----------
sessions                          170             170        170        170

2. Increase the session parameter has effect with three parameters. These parameter has relation in between:

–for 10g and above

processes=x

sessions=x*1.1+5

transactions=sessions*1.1

———-for 12c

SESSIONS = (1.5 * PROCESSES) + 22

— For 18c

maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

sql> show parameter sessions
sql> show parameter processes
sql> show parameter transactions

3. You need to increase the PROCESSES parameter only, Oracle will automatically set all other parameters.

-- Increase the value of Processes parameter
alter system set processes=500 scope=spfile;

-- Reboot will take effect.
shutdown immediate
startup

4. Verified session parameter is modified according to formula

SQL> show parameter sessions;

For PDB and CDB, you cannot set the Session parameter at CDB$ROOT Database, but in PDB database you can set it upto the upper limit show in CDB$ROOT database. you can alter session parameter in PDBs database as shown below:

-- Connect with CDB$ROOT
SQLPLUS SYS AS SYSDBA

-- show parameter sessions

NAME                 TYPE        VALUE
-------------------- ----------- ----------
sessions             integer     776
-- Connect with PDB database
SQL> conn test@xepdb1
Enter password:
Connected.

--Check session parameter at PDB level
SQL> show parameter sessions

NAME               TYPE        VALUE
------------------ ----------- -----------
sessions           integer     300
-- Alter sessions at PDB level
SQL> alter system set sessions=776 scope=both;
System altered.

-- If you go exceed the sessions parameter above CDB$ROOT then got error
SQL> alter system set sessions=777 scope=both;
alter system set sessions=777 scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00047: cannot set sessions parameter to this value