blocks|key|1731367|text|我认为这是可行的，基于this+source。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1731368|SELECT
++'Currently,+'+
++%7C%7C+(SELECT+COUNT(*)+FROM+V$SESSION)
++%7C%7C+'+out+of+'+
++%7C%7C+DECODE(VL.SESSIONS_MAX,0,'unlimited',VL.SESSIONS_MAX)+
++%7C%7C+'+connections+are+used.'+AS+USAGE_MESSAGE
FROM+
++V$LICENSE+VL|code-block|syntax|javascript|1731369|然而，贾斯汀·凯夫是对的。此查询可提供更好的结果：|1731370|SELECT
++'Currently,+'+
++%7C%7C+(SELECT+COUNT(*)+FROM+V$SESSION)
++%7C%7C+'+out+of+'+
++%7C%7C+VP.VALUE+
++%7C%7C+'+connections+are+used.'+AS+USAGE_MESSAGE
FROM+
++V$PARAMETER+VP
WHERE+VP.NAME+=+'sessions'|1731371|entityMap|0|LINK|mutability|MUTABLE|url|http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1144.htm^0|B|B|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

I thought this would work, based on <a href="http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1144.htm" rel="noreferrer">this source</a>.

<pre><code>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
</code></pre>

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

<pre><code>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'
</code></pre>

blocks|key|1731478|text|在确定Oracle数据库支持的连接数量时，可能存在一些不同的限制。最简单的方法是使用会话参数和V$SESSION，即|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1731479|数据库配置为允许的会话数|1731480|SELECT+name,+value+
++FROM+v$parameter
+WHERE+name+=+'sessions'|code-block|syntax|javascript|1731481|当前活动的会话数量|1731482|SELECT+COUNT(*)
++FROM+v$session|1731483|但是，正如我所说的，在数据库级别和操作系统级别都有其他潜在的限制，这取决于是否配置了共享服务器。如果忽略共享服务器，则在达到会话参数的限制之前，很可能会达到进程参数的限制。而且您可能会遇到操作系统的限制，因为每个会话都需要一定数量的RAM。|1731484|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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

<pre><code>SELECT name, value 
 FROM v$parameter
 WHERE name = 'sessions'
</code></pre>

The number of sessions currently active

<pre><code>SELECT COUNT(*)
 FROM v$session
</code></pre>

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.

blocks|key|1516405|text|select+count(*),sum(decode(status,+'ACTIVE',1,0))+from+v$session+where+type=+'USER'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1516406|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select count(*),sum(decode(status, 'ACTIVE',1,0)) from v$session where type= 'USER'
</code></pre>

blocks|key|1516513|text|注意:这只回答了问题的一部分。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1516514|如果只想知道允许的最大会话数，那么可以在sqlplus中以sysdba的身份执行：|1516515|SQL>+show+parameter+sessions|code-block|syntax|javascript|1516516|这将为您提供类似如下的输出：|1516517|++++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|1516518|sessions参数是您想要的参数。|1516519|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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:

<pre><code>SQL&gt; show parameter sessions
</code></pre>

This gives you an output like:

<pre><code> 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
</code></pre>

The sessions parameter is the one what you want.

blocks|key|1731620|text|如果您想要获取整个集群的会话总数，请使用gv$session+for+RAC。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1731621|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1731668|text|我对v$resource_limit视图非常感兴趣，以便浏览oracle会话、进程..：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1731669|https://bbdd-error.blogspot.com.es/2017/09/check-sessions-and-processes-limit-in.html|offset|length|1731670|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2D|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

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

<a href="https://bbdd-error.blogspot.com.es/2017/09/check-sessions-and-processes-limit-in.html" rel="nofollow noreferrer">https://bbdd-error.blogspot.com.es/2017/09/check-sessions-and-processes-limit-in.html</a>

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

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

Oracle数据库

数据库

服务器

使用SQL检查Oracle数据库允许的最大连接数的最佳方法是什么？最后，我想显示当前会话的数量和允许的总数量，例如“目前，80个连接中有23个被使用”。

问如何检查Oracle数据库允许的最大连接数？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何检查Oracle数据库允许的最大连接数？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何检查Oracle数据库允许的最大连接数？
EN