204
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.
- 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
- 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
- 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
- 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