获取已建立的与Oracle 11的连接的IP地址 [英] Get IP addresses of established connections to Oracle 11

问题描述

在开发过程中，我发现数据库具有大量的活动连接，方法是:

During development I found that database have large number of lived connections by:

SELECT username, COUNT(*) FROM v$session GROUP BY username;

为了找到实际保持连接的人，我想获得一个IP地址列表.

In order to find who actually hold connection I want to get a list of IP addresses.

在常规网络搜索过程中，阅读并阅读官方文档我建立查询:

During general web search and reading official docs I build query:

SELECT username, seconds_in_wait, machine, port, terminal, program, module, service_name
  FROM v$session
  WHERE type = 'USER';

其中，machine是select的最重要部分.但是很遗憾，machine字段显示客户端操作系统已知的主机名.

where machine is most important part of select. But unfortunately machine field shows host name known by client OS.

Internet推荐使用 UTL_INADDR.GET_HOST_ADDRESS 这不适用于我的情况.首先是由于 ORA-24247:访问控制列表(ACL)拒绝了网络访问，其次是因为客户端操作系统主机名通常在/etc/hostname 中定义strong>，并且我们的Intranet中的DNS服务器不可用...

Internet full of recommendation to use UTL_INADDR.GET_HOST_ADDRESS which is not applicable in my case. Firstly because of ORA-24247: network access denied by access control list (ACL) and secondly because client OS host name usually defined in /etc/hostname and doesn't available to DNS server in our intranet...

以其他方式检索到Oracle DB的开放会话的IP(无论如何，数据库实例都保留有关其套接字的信息...).

Any other way to retrieve IP of open session to Oracle DB (DB instance hold information about its sockets in any case...).

更新

我位于受信任的Intranet下，但网络层次结构未知.

I under trusted intranet but with unknown network hierarchy.

然后我尝试查找哪些应用程序使用了我的表(几个应用程序服务器，我并不全部知道).其中一些过度使用了连接，需要进行修复.但是首先应该确定它们...

And I try to find which applications use my tables (several app-servers, I don't know all of them). Some of them overuse connections and need to be fixed. But firstly they should be identified...

推荐答案

请记住，Oracle会话不需要知道，当然也不需要信任即将到来的客户端名称/IP地址从;它位于网络传输层之上，实际上并不关心您是通过TCP/IP还是其他方式进行连接. (我什至不确定侦听器是否必须传递信息，或者是否有效地传递了现成的套接字).如您所见，machine就是客户端声明的内容，例如program和v$session视图中的其他字段.它可能与DNS或服务器的/etc/hosts不能解析的任何内容相似，特别是如果客户端是Windows盒.

Bear in mind that the Oracle session doesn't need to know, and certainly doesn't need to trust, the client name/IP address you're coming from; it's sitting above the network transport layer, and doesn't really care if you're connected over TCP/IP or something else. (I'm not even sure if the listener has to pass the info across, or if it effectively passes a ready-made socket). As you've seen the machine is just what the client declared, like program and other fields in the v$session view; it may not bear any resemblance to anything that DNS or your server's /etc/hosts can resolve, particularly if the client is a Windows box.

您可以做的是，在Unix/Linux级别上(由于您引用的是/etc/hosts，所以我认为您不在Windows上)，请查找port并查看显示的地址；例如v$session将我的port显示为50527，因此如果执行netstat -an | grep 50527，我会看到:

What you could do is, at Unix/Linux level (since you refer to /etc/hosts, I assume you aren't on Windows), look for the port and see what address that shows; for example v$session shows my port as 50527, so if I do netstat -an | grep 50527 I see:

tcp  0  0  192.168.1.1:1521  192.168.1.23:50527  ESTABLISHED

所以我可以看到我已从192.168.1.23连接.如果您在服务器上运行SQL * Plus，则可以使用host命令执行此操作，但这仍然有些不便.如果您需要定期执行此操作，并且无法添加登录触发器以将其捕获到审计表中，并且确实需要从数据库中执行此操作，则可能可以编写Java存储过程来从中进行查找该端口为您服务.但是编写一个shell脚本从v$session查询port数字并以这种方式进行查找可能更容易.

So I can see I'm connected from 192.168.1.23. You can do that with a host command if you're running SQL*Plus on the server, but it's still a bit inconvenient. If you needed to do this regularly, and adding a logon trigger to capture it to an audit table isn't an option, and you really had to do it from within the database you could probably write a Java stored procedure to do the lookup from that port for you. But it's probably easier to write a shell script to query the port numbers from v$session and do the lookup that way round.

这篇关于获取已建立的与Oracle 11的连接的IP地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！