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
Smart way of Technology 