Skip to content

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.

If using spfile, then do the following:

SQL> alter system set sessions=<new value> scope=spfile;
SQL> shutdown immediate
SQL> startup

-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+)

SESSIONS specifies the total number of user and system sessions. The default number is greater than PROCESSES to allow for recursive sessions. Recursive sessions are not reflected in the v$session view. Recommendation is to preserve 50% of the SESSIONS value for recursive sessions.

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

No comments yet

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

%d bloggers like this: