SQL 如何检查允许连接到 Oracle 数据库的最大数量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/162255/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check the maximum number of allowed connections to an Oracle database?
提问by Niklas Gustavsson
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".
使用 SQL 检查 Oracle 数据库允许的最大连接数的最佳方法是什么？最后，我想显示当前的会话数和允许的总数，例如“目前，使用了 80 个连接中的 23 个”。
回答by Justin Cave
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.
在确定 Oracle 数据库支持的连接数时，可能会出现一些不同的限制。最简单的方法是使用 SESSIONS 参数和 V$SESSION，即
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.
但是，正如我所说，在数据库级别和操作系统级别以及是否已配置共享服务器都存在其他潜在限制。如果忽略共享服务器，您很可能会在达到 SESSIONS 参数的限制之前达到 PROCESSES 参数的限制。而且您可能会达到操作系统限制，因为每个会话都需要一定数量的 RAM。
回答by FuePi
The sessionsparameter is derived from the processesparameter and changes accordingly when you change the number of max processes. See the Oracle docsfor further info.
该会话参数从推导过程参数，当你改变最大进程数随之改变。有关更多信息，请参阅Oracle 文档。
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
回答by JosephStyons
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'
回答by Tom
Use gv$session for RAC, if you want get the total number of session across the cluster.
如果您想获得整个集群的会话总数，请对 RAC 使用 gv$session。
回答by botkop
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:
如果只想知道允许的最大会话数，那么可以在sqlplus中执行，作为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.
session 参数就是你想要的。
回答by fdominguez.bbdd
v$resource_limit view is so interesting for me in order to glance oracle sessions,processes..:
v$resource_limit 视图对我来说非常有趣，以便浏览 oracle 会话、进程..：
https://bbdd-error.blogspot.com.es/2017/09/check-sessions-and-processes-limit-in.html
https://bbdd-error.blogspot.com.es/2017/09/check-sessions-and-processes-limit-in.html
回答by saris mohammad
select count(*),sum(decode(status, 'ACTIVE',1,0)) from v$session where type= 'USER'
