ORA-00018-Maximum number of sessions exceeded.

The ORA-00018 error means that the maximum number of sessions allowed by the Oracle database has been reached.

ORA-00018-Maximum number of sessions exceeded

Cause:

This can happen if there are too many users trying to connect to the database at the same time, or if a single user is running a long-running query or transaction.

Resolution:

To solve this problem, you need to increase the sessions parameters. Sessions parameters depend on processes and transactions parameters.

Step 1:

Check the v$session view to see a list of all the current sessions in the database. This can help you identify which sessions are using the most resources and may be causing the error.

From Above query used by V$SESSION that The sessions parameter is 170 while count shows only 163 sessions are running. the difference is of 7 sessions left. which shows something wrong.

Step 2:

Check the v$resource_limit view to see the current maximum number of sessions allowed by the database. This can help you verify that the SESSIONS parameter has been set correctly.
To get more visibility, let’s double check from V$RESOURCE_LIMIT which shows us the usage of various fixed and segmented arrays in Oracle instance, including the session state object array, V$SESSION:

Step 3:

If you are using a load balancer, check the load balancer’s logs to see if it is rejecting any connections to the database.

Step 4:

If you are using a firewall, check the firewall’s rules to make sure that it is not blocking any connections to the database.

Step 5:

Method-1

To fix the ORA-00018 error, you can increase the maximum number of sessions allowed by the database. This can be done by changing the SESSIONS initialization parameter in the database’s configuration file.
To increase the SESSIONS parameter, you will need to edit the database’s configuration file. The configuration file is typically named init.ora and is located in the ORACLE_HOME/dbs directory.
To increase the SESSIONS parameter, you will need to change the value of the parameter. The new value should be larger than the current number of sessions that are connected to the database. For example, if the current number of sessions is 100, you would need to change the value of the SESSIONS parameter to 150.

Method- 2

You can check for idle or inactive sessions and terminate them to free up resources. Run the following SQL query as a privileged user to identify idle sessions:

SELECT sid, serial#, username, machine
FROM v$session
WHERE status = ‘INACTIVE’ AND idle_time > ;

Once you have identified the sessions, you can terminate them using the ALTER SYSTEM KILL SESSION command:

ALTER SYSTEM KILL SESSION ‘<side>,<serial#>’ ;

Method-3

You can increase these 3 parameters according to following formula.

processes=x
sessions=x1.2 transactions=sessions1.2

If you are planning to increase “sessions” parameter you should also plan to increase “processes” and “transactions” parameters.

SQL> alter system set processes=1200 scope=spfile;
SQL> alter system set sessions=1200 scope=spfile;
SQL> alter system set transactions=1440 scope=spfile;

Step 6:

Once you have changed the value of the SESSIONS parameter, you will need to save the configuration file and restart the database.

After the database has restarted, you should be able to connect to the database without receiving the ORA-00018 error.

SQL> shutdown immediate
SQL> startup

It’s important to note that modifying database parameters and terminating sessions should be performed carefully and with proper understanding in order to resolve error ORA-00018-Maximum number of sessions exceeded. If you’re unsure or need further assistance, it’s recommended to consult with a database administrator or Oracle support.

How to Resolve ORA-00001: unique constraint violated