ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded

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

Maximum number of session parameter value is reached.

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';

----------------- ------------------- --------------- ---------- ----------
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
———-for 12c
— 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

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

-- show parameter sessions

NAME                 TYPE        VALUE
-------------------- ----------- ----------
sessions             integer     776

-- Connect with PDB database
SQL> conn test@xepdb1
Enter password:

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