ORA-00018 maximum number of sessions exceeded

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.