ORA-00018 maximum number of sessions exceeded

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

ORA-00018 maximum number of sessions exceeded

This is the Oracle session count:

SQL> select count(*) from gv$session;

COUNT(*)
----------
75

And this is the limit that's set in Oracle:

SQL> 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
session_cached_cursors integer 50
session_max_open_files integer 10
sessions  integer 1536
shared_server_sessions integer 40

Environment

Release:
Component: CAUIM

Resolution

On the Oracle server, run this command

alter system set shared_server_sessions=150 scope=both;

Before

SQL> 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
session_cached_cursors integer 50
session_max_open_files integer 10
sessions integer 1536
shared_server_sessions integer 40

To 150:

SQL> show parameter shared

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer 40
shared_memory_address integer 0
shared_pool_reserved_size big integer 27682406
shared_pool_size big integer 0
shared_server_sessions integer 150
shared_servers  integer 5