blocks|key|1305768|text|有关更完整的答案，请参阅：http://dbaforums.org/oracle/index.php?showtopic=16834|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1305769|select
+++++++substr(a.spid,1,9)+pid,
+++++++substr(b.sid,1,5)+sid,
+++++++substr(b.serial#,1,5)+ser#,
+++++++substr(b.machine,1,6)+box,
+++++++substr(b.username,1,10)+username,
--+++++++b.server,
+++++++substr(b.osuser,1,8)+os_user,
+++++++substr(b.program,1,30)+program
from+v$session+b,+v$process+a
where
b.paddr+=+a.addr
and+type='USER'
order+by+spid;+|code-block|syntax|javascript|1305770|entityMap|0|LINK|mutability|MUTABLE|url|http://dbaforums.org/oracle/index.php?showtopic=16834^0|D|1H|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

For a more complete answer see:
 <a href="http://dbaforums.org/oracle/index.php?showtopic=16834">http://dbaforums.org/oracle/index.php?showtopic=16834</a>

<pre><code>select
 substr(a.spid,1,9) pid,
 substr(b.sid,1,5) sid,
 substr(b.serial#,1,5) ser#,
 substr(b.machine,1,6) box,
 substr(b.username,1,10) username,
-- b.server,
 substr(b.osuser,1,8) os_user,
 substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid; 
</code></pre>

blocks|key|1520857|text|当我想要查看从我们的应用服务器到数据库的传入连接时，我使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1520858|SELECT+username+FROM+v$session+
WHERE+username+IS+NOT+NULL+
ORDER+BY+username+ASC;|code-block|syntax|javascript|1520859|简单，但有效。|1520860|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

When I'd like to view incoming connections from our application servers to the database I use the following command:

<pre><code>SELECT username FROM v$session 
WHERE username IS NOT NULL 
ORDER BY username ASC;
</code></pre>

Simple, but effective.

blocks|key|1520870|text|select
++username,
++osuser,
++terminal,
++utl_inaddr.get_host_address(terminal)+IP_ADDRESS
from
++v$session
where
++username+is+not+null
order+by
++username,
++osuser;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1520871|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
 username,
 osuser,
 terminal,
 utl_inaddr.get_host_address(terminal) IP_ADDRESS
from
 v$session
where
 username is not null
order by
 username,
 osuser;
</code></pre>

blocks|key|1520881|text|Select+count(1)+From+V$session
where+status='ACTIVE'
/|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1520882|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(1) From V$session
where status='ACTIVE'
/
</code></pre>

blocks|key|1305861|text|select+status,+count(1)+as+connectionCount+from+V$SESSION+group+by+status;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1305862|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 status, count(1) as connectionCount from V$SESSION group by status;
</code></pre>

blocks|key|1520940|text|下面给出了按连接数排序的操作系统用户列表，这在查找过度使用资源时非常有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1520941|select+osuser,+count(*)+as+active_conn_count+
from+v$session+
group+by+osuser+
order+by+active_conn_count+desc|code-block|syntax|javascript|1520942|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The following gives you list of operating system users sorted by number of connections, which is useful when looking for excessive resource usage.

<pre><code>select osuser, count(*) as active_conn_count 
from v$session 
group by osuser 
order by active_conn_count desc
</code></pre>

blocks|key|1520951|text|select+
++++count(1)+"NO.+Of+DB+Users",+
++++to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')+sys_time
from+
++++v$session+
where+
++++username+is+NOT++NULL;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1520952|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(1) "NO. Of DB Users", 
 to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') sys_time
from 
 v$session 
where 
 username is NOT NULL;
</code></pre>

blocks|key|1520978|text|select+s.sid+as+"Sid",+s.serial#+as+"Serial#",+nvl(s.username,+'+')+as+"Username",+s.machine+as+"Machine",+s.schemaname+as+"Schema+name",+s.logon_time+as+"Login+time",+s.program+as+"Program",+s.osuser+as+"Os+user",+s.status+as+"Status",+nvl(s.process,+'+')+as+"OS+Process+id"
from+v$session+s
where+nvl(s.username,+'a')+not+like+'a'+and+status+like+'ACTIVE'
order+by+1,2|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1520979|此查询试图过滤掉所有后台进程。|unstyled|1520980|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select s.sid as "Sid", s.serial# as "Serial#", nvl(s.username, ' ') as "Username", s.machine as "Machine", s.schemaname as "Schema name", s.logon_time as "Login time", s.program as "Program", s.osuser as "Os user", s.status as "Status", nvl(s.process, ' ') as "OS Process id"
from v$session s
where nvl(s.username, 'a') not like 'a' and status like 'ACTIVE'
order by 1,2
</code></pre>

This query attempts to filter out all background processes.

Is there any hidden table, system variable or something to show active connections in a given moment?

How to list active / open connections in Oracle?

数据库

服务器

是否有任何隐藏表、系统变量或其他东西来显示给定时刻的活动连接？

问如何在Oracle中列出活动/打开的连接？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中列出活动/打开的连接？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中列出活动/打开的连接？
EN