Troubleshooting Guide – ORA-00018: Maximum Number Of Sessions (%S) Exceeded

SYMPTOMS

You receive the following errors when trying to make new connections:

ORA-00604: error occurred at recursive SQL level 1
Cause: An error occurred while processing a recursive SQL statement.
(a statement applying to internal dictionary tables).
Action: If the situation described in the next message on the stack can be corrected, do so; otherwise, contact customer support.

ORA-00018: maximum number of sessions exceeded
Cause: An operation requested a resource that was unavailable.
The maximum number of sessions is specified by the initialization parameter SESSIONS.
When this maximum is reached, no more requests are processed.
Action: Try the operation again in a few minutes.
If this message occurs often, shut down Oracle, increase the SESSIONS parameter in the initialization parameter file, and restart Oracle.

NOTE: No new connections are permitted to the database.

However when querying v$resource_limit it shows this is not true.

SQL> select * from v$resource_limit;

RESOURCE_NAME      CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------ ------------------- --------------- ---------- ----------
processes                          538             596       1365       1365
sessions                           651             744       4096       4096

show parameter session;

NAME                              TYPE        VALUE
--------------------------------- --------  -------
java_max_sessionspace_size        integer         0
java_soft_sessionspace_limit      integer         0
license_max_sessions              integer         0
license_sessions_warning          integer         0
logmnr_max_persistent_sessions    integer         1
session_cached_cursors            integer        20
session_max_open_files            integer        10
sessions                          integer      4096
shared_server_sessions            integer       100

At the time of the error:

Select count(*) from v$session;

COUNT(*)
----------
587

select count(*) from x$ksuse where bitand(ksspaflg,1) !=0 ;

COUNT(*)
----------
618 

As seen the current number of running sessions is no where near the settings for the sessions parameter yet the following error is still reported:
ORA-18 MAXIMUM NUMBER OF SESSIONS EXCEEDED

CAUSE

Cause#1:
The maximum number of sessions in the database have been exceeded

Cause#2:
This issue has been addressed in the following bug that has been closed as not a bug:
Bug:11825730 – ORA-18 MAXIMUM NUMBER OF SESSIONS EXCEEDED

Development explained that the problem is due to shared server being enabled and the shared_server_sessions is set to a low value. Verified by the output from the event 18 trace file:

038001B3A4 kmmsgism = 4096 max instance sessions
038001B3A8 kmmsgmsm = 100 max shared server sessions
038001B3AC kmmsgnsm = 100 current # of shared server sessions

SOLUTION

Solution#1:

Increase the value of the SESSIONS parameter in the parameter file.

-or-
Increase the value of PROCESSES since SESSIONS is derived off of the PROCESSES parameter.
SESSIONS:      derived (1.1 * PROCESSES) + 5 (versions prior to 11.2)
SESSIONS:      derived (1.8 * PROCESSES) + 22 (11.2+)

Solution#2:

Limit the number of sessions per user  using resource limit profiles or resource manager. Refer  Limit Maxmimum Concurrent Sessions on Database Excluding Background Processes

Solution#3:
1. Increase the value for the init parameter shared_server_sessions
– OR –
2. Disable shared server

By default when shared server is configured, the connection made to the DB will be shared connections unless explicitly specified in the tnsnames.ora file that the service is using dedicated connections. Therefore if you do not intend to use shared server connections please disable shared server to allow for dedicated connections as follows. You disable shared server by setting SHARED_SERVERS to 0. No new client can connect in shared mode. However, when you set SHARED_SERVERS to 0, Oracle Database retains some shared servers until all shared server connections are closed. The number of shared servers retained is either the number specified by the preceding setting of
SHARED_SERVERS or the value of the MAX_SHARED_SERVERS parameter, whichever is smaller. If both SHARED_SERVERS and MAX_SHARED_SERVERS are set to 0, then all shared servers will terminate and requests from remaining shared server clients will be queued until the value of SHARED_SERVERS or MAX_SHARED_SERVERS is raised again.

After you disable shared server, all connections will be dedicated by default.

Solution#4:
Check if there is any user or application creating excessive sessions. Contact the application vendor in order to check why there are so many opened sessions (possibly sessions leak at the application level).