Troubleshooting Guide – ORA-00018: Maximum Number Of Sessions (%S) Exceeded
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
The maximum number of sessions in the database have been exceeded
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
Increase the value of the SESSIONS parameter in the parameter file.
If using spfile, then do the following:
SQL> shutdown immediate
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+)
Limit the number of sessions per user using resource limit profiles or resource manager. Refer Limit Maxmimum Concurrent Sessions on Database Excluding Background Processes
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.
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).