99

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

Niklas Gustavsson
  • 1,223
  • 1
  • 9
  • 8

7 Answers7

132

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 1
    sorry, how can we run this query? I'm receiving "table or view not exist" for "SELECT COUNT(*) FROM v$session" – lorraine batol Jul 21 '16 at 09:16
  • 5
    @yin03 - That implies that whatever Oracle user you are using doesn't have privileges on the `v$session` view. You'd need to ask your DBA to grant you that privilege. Most likely, you'd want the `select any dictionary` privilege though the `select_catalog_role` role or a direct grant on just that object would also work. – Justin Cave Jul 21 '16 at 17:30
  • ORA-00942: "table or view does not exist" – pdem Jan 19 '21 at 15:14
  • 1
    @pdem - As my prior comment mentions, that means that you don't have permission to access those views and need to ask the DBA to grant them to you. – Justin Cave Jan 19 '21 at 16:19
45

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
FuePi
  • 1,958
  • 22
  • 18
35

I thought this would work, based on this source.

SELECT
  'Currently, ' 
  || (SELECT COUNT(*) FROM V$SESSION)
  || ' out of ' 
  || DECODE(VL.SESSIONS_MAX,0,'unlimited',VL.SESSIONS_MAX) 
  || ' connections are used.' AS USAGE_MESSAGE
FROM 
  V$LICENSE VL

However, Justin Cave is right. This query gives better results:

SELECT
  'Currently, ' 
  || (SELECT COUNT(*) FROM V$SESSION)
  || ' out of ' 
  || VP.VALUE 
  || ' connections are used.' AS USAGE_MESSAGE
FROM 
  V$PARAMETER VP
WHERE VP.NAME = 'sessions'
JosephStyons
  • 57,317
  • 63
  • 160
  • 234
6

Note: this only answers part of the question.

If you just want to know the maximum number of sessions allowed, then you can execute in sqlplus, as sysdba:

SQL> show parameter sessions

This gives you an output like:

    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
sessions                             integer     248
shared_server_sessions               integer

The sessions parameter is the one what you want.

botkop
  • 934
  • 1
  • 8
  • 17
4

Use gv$session for RAC, if you want get the total number of session across the cluster.

Tom
  • 41
  • 1
3

v$resource_limit view is so interesting for me in order to glance oracle sessions,processes..:

https://bbdd-error.blogspot.com.es/2017/09/check-sessions-and-processes-limit-in.html

fdominguez.bbdd
  • 31
  • 2
1
select count(*),sum(decode(status, 'ACTIVE',1,0)) from v$session where type= 'USER'
ceving
  • 21,900
  • 13
  • 104
  • 178
saris mohammad
  • 11
  • 1