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".
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.
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
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
Use gv$session for RAC, if you want get the total number of session across the cluster.
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'
select count(*),sum(decode(status, 'ACTIVE',1,0)) from v$session where type= 'USER'
Source: Stackoverflow.com