How to check the maximum number of allowed connections to an Oracle database?

Question

What's the best way, using SQL, to check the maximum number of connections that is allowed for an Oracle database? In the end, I would like to show the current number of sessions and the total number allowed, e.g. "Currently, 23 out of 80 connections are used".

Answer 1

There are a few different limits that might come in to play in determining the number of connections an Oracle database supports. The simplest approach would be to use the SESSIONS parameter and V$SESSION, i.e.

The number of sessions the database was configured to allow

SELECT name, value    FROM v$parameter  WHERE name = 'sessions' 

The number of sessions currently active

SELECT COUNT(*)   FROM v$session 

As I said, though, there are other potential limits both at the database level and at the operating system level and depending on whether shared server has been configured. If shared server is ignored, you may well hit the limit of the PROCESSES parameter before you hit the limit of the SESSIONS parameter. And you may hit operating system limits because each session requires a certain amount of RAM.

Answer 2

The sessions parameter is derived from the processes parameter and changes accordingly when you change the number of max processes. See the Oracle docs for further info.

To get only the info about the sessions:

    select current_utilization, limit_value      from v$resource_limit      where resource_name='sessions'; 

 CURRENT_UTILIZATION LIMIT_VALUE ------------------- -----------                 110         792 

Try this to show info about both:

    select resource_name, current_utilization, max_utilization, limit_value      from v$resource_limit      where resource_name in ('sessions', 'processes'); 

 RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE ------------- ------------------- --------------- ----------- processes                      96             309         500 sessions                      104             323         792