获取到 Oracle 11 的已建立连接的 IP 地址
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15069145/
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
Get IP addresses of established connections to Oracle 11
提问by gavenkoa
During development I found that database have large number of lived connections by:
在开发过程中，我发现数据库通过以下方式拥有大量实时连接：
SELECT username, COUNT(*) FROM v$session GROUP BY username;
In order to find who actually hold connection I want to get a list of IP addresses.
为了找到谁实际持有连接，我想获得一个 IP 地址列表。
During general web search and reading official docsI build query:
在一般的网络搜索和阅读官方文档期间，我构建查询：
SELECT username, seconds_in_wait, machine, port, terminal, program, module, service_name
FROM v$session
WHERE type = 'USER';
where machineis most important part of select. But unfortunately machinefield shows host name known by client OS.
machine最重要的部分在哪里select。但不幸的是，machine字段显示了客户端操作系统已知的主机名。
Internet full of recommendation to use UTL_INADDR.GET_HOST_ADDRESSwhich 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 nameusually defined in /etc/hostnameand doesn't available to DNS server in our intranet...
互联网上充满了使用UTL_INADDR.GET_HOST_ADDRESS的建议，这在我的情况下不适用。首先是因为ORA-24247：网络访问被访问控制列表 (ACL) 拒绝，其次是因为客户端操作系统主机名通常定义在/etc/hostname 中，并且不能用于我们内部网中的 DNS 服务器...
Any other way to retrieve IP of open session to Oracle DB (DB instance hold information about its sockets in any case...).
将打开会话的 IP 检索到 Oracle DB 的任何其他方式（数据库实例在任何情况下都保存有关其套接字的信息......）。
UPDATE
更新
I under trusted intranet but with unknown network hierarchy.
我在受信任的 Intranet 下，但网络层次结构未知。
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...
我试图找出哪些应用程序使用我的表（几个应用程序服务器，我不知道所有这些）。其中一些过度使用连接，需要修复。但首先他们应该被识别......
采纳答案by Alex Poole
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 machineis just what the client declared, like programand other fields in the v$sessionview; it may not bear any resemblance to anything that DNS or your server's /etc/hostscan resolve, particularly if the client is a Windows box.
请记住，Oracle 会话不需要知道，当然也不需要信任您来自的客户端名称/IP 地址；它位于网络传输层之上，并不真正关心您是否通过 TCP/IP 或其他方式连接。（我什至不确定侦听器是否必须传递信息，或者它是否有效地传递了现成的套接字）。正如您所看到的machine，这正是客户端声明的内容，例如视图中的program其他字段v$session；它可能与 DNS 或您的服务器/etc/hosts可以解析的任何内容没有任何相似之处，特别是如果客户端是 Windows 机器。
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 portand see what address that shows; for example v$sessionshows my portas 50527, so if I do netstat -an | grep 50527I see:
您可以做的是，在 Unix/Linux 级别（因为您指的是 /etc/hosts，我假设您不在 Windows 上），查找port并查看显示的地址；例如v$session显示我的portas 50527，所以如果我这样做，netstat -an | grep 50527我会看到：
tcp 0 0 192.168.1.1:1521 192.168.1.23:50527 ESTABLISHED
So I can see I'm connected from 192.168.1.23. You can do that with a hostcommand 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 portnumbers from v$sessionand do the lookup that way round.
所以我可以看到我是从192.168.1.23. host如果您在服务器上运行 SQL*Plus，则可以使用命令来执行此操作，但这仍然有点不方便。如果您需要定期执行此操作，并且添加登录触发器以将其捕获到审计表中不是一种选择，并且您确实必须从数据库中执行此操作，则您可能会编写一个 Java 存储过程来进行查找那个端口给你。但是编写一个 shell 脚本来查询port数字v$session并以这种方式进行查找可能更容易。
回答by gavenkoa
Thanks to all for digging into my question (which is still general purpose, not exactly mine!!).
感谢所有人深入研究我的问题（这仍然是通用目的，不完全是我的！！）。
Just short answer: you can't get real IP from Oracle system tables.
只是简短的回答：您无法从 Oracle 系统表中获得真正的 IP。
For this tasks you can use general purpose utilities like netstator lsof -p <pid-of-oracle>but only on server side!
对于此任务，您可以使用通用实用程序，例如netstat或lsof -p <pid-of-oracle>但只能在服务器端使用！
Some help can come from v$session.portvalues...
一些帮助可以来自v$session.port价值观......
One good suggestion from helpers - use good names from DB clients. They are populated to v$sessiontable rows:
帮助者的一个好建议 - 使用数据库客户端的好名字。它们填充到v$session表行：
machine, terminal, program, module, service_name
so they can help to identify clients...
所以他们可以帮助识别客户......
回答by Ken Banyas
The IP address of an incoming connection can usually be found in your Listener log. That is how I track such information when I need to.
传入连接的 IP 地址通常可以在您的监听器日志中找到。这就是我在需要时跟踪此类信息的方式。
回答by T-Gergely
I've found my auditing script from 2001 when logon triggers didn't exist AFAIK. It parses the COMMENT$TEXT column of SYS.AUD$ where ACTION#=100 to get the IP address of the client. That column still contains Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=a.a.a.a)(PORT=p))in our environments.
当登录触发器不存在 AFAIK 时，我从 2001 年找到了我的审计脚本。它解析 SYS.AUD$ 的 COMMENT$TEXT 列，其中 ACTION#=100 以获取客户端的 IP 地址。该列仍包含Authenticated by: DATABASE; 客户端地址： (ADDRESS=(PROTOCOL=tcp)(HOST= aaaa)(PORT= p))在我们的环境中。
