oracle 获取oracle客户端用户的IP地址
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28847063/
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 address of oracle client users
提问by user2470764
I need a query to get all IP's of client users that have already a session to the database, I have an Oracle Database server in my work (hospital), some times the database stop working and can't execute any query that done by Oracle modules of the system which is the interface of the users, so to solve it we have to kill all sessions that connected to the database and make all users to restart sessions, my question is there any way to get the ip address with any information of the session like the consumption of the session?
我需要一个查询来获取已经与数据库建立会话的客户端用户的所有 IP，我的工作（医院）中有一个 Oracle 数据库服务器，有时数据库停止工作并且无法执行由 Oracle 完成的任何查询系统的模块是用户的接口，所以要解决这个问题，我们必须杀死所有连接到数据库的会话并让所有用户重新启动会话，我的问题是有什么方法可以获取包含任何信息的 IP 地址session 喜欢消费session吗？
回答by Lalit Kumar B
I need a query to get all IP's of client users that have already a session to the database
我需要一个查询来获取已经与数据库建立会话的客户端用户的所有 IP
You could use SYS_CONTEXT. It would return the following host and IP address information for the current session:
您可以使用SYS_CONTEXT。它将返回当前会话的以下主机和 IP 地址信息：
- TERMINAL - An operating system identifier for the current session. This is often the client machine name.
- HOST - The host name of the client machine.
- IP_ADDRESS - The IP address of the client machine.
- SERVER_HOST - The host name of the server running the database instance.
- TERMINAL - 当前会话的操作系统标识符。这通常是客户端计算机名称。
- HOST - 客户端机器的主机名。
- IP_ADDRESS - 客户端机器的 IP 地址。
- SERVER_HOST - 运行数据库实例的服务器的主机名。
Have a look at this articleby Tim Hall.
看看Tim Hall 的这篇文章。
For example,
例如，
SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
----------------------------------------------------------
127.0.0.1
SQL>
For me the IP is localhost, so I get 127.0.0.1
对我来说，IP 是localhost，所以我得到127.0.0.1
EditFrom discussions in the comments below, to get the list of the IP address of all the users from v$session, you could use MACHINEthey are connected from.
编辑从下面评论中的讨论中，要从 v$session 获取所有用户的 IP 地址列表，您可以使用MACHINE它们连接的来源。
SELECT utl_inaddr.get_host_address(t.machine), t.* FROM v$session t;
SELECT utl_inaddr.get_host_address(t.machine), t.* FROM v$session t;
回答by Wernfried Domscheit
Have a look with this query:
看看这个查询：
SELECT username, status, osuser, process, machine, terminal, logon_time,
lockwait, blocking_session_status, blocking_instance, blocking_session,
UTL_INADDR.GET_HOST_ADDRESS(REGEXP_REPLACE(machine, '^.+\')) AS client_ip
FROM v$session;
