title: Get IP addresses of established connections to Oracle 11
body: <p>During development I found that database have large number of lived connections by:</p>
  
  <pre><code>SELECT username, COUNT(*) FROM v$session GROUP BY username;
  </code></pre>
  
  <p>In order to find who actually hold connection I want to get a list of IP addresses.</p>
  
  <p>During general web search and reading <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2088.htm" rel="nofollow">official docs</a> I build query:</p>
  
  <pre><code>SELECT username, seconds_in_wait, machine, port, terminal, program, module, service_name
    FROM v$session
    WHERE type = 'USER';
  </code></pre>
  
  <p>where <code>machine</code> is most important part of <code>select</code>. But unfortunately <code>machine</code> field shows <em>host name known by client OS</em>.</p>
  
  <p>Internet full of recommendation to use <a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_inaddr.htm" rel="nofollow">UTL_INADDR.GET_HOST_ADDRESS</a> which is not applicable in my case. Firstly because of <strong>ORA-24247: network access denied by access control list (ACL)</strong> and secondly because <em>client OS host name</em> usually defined in <strong>/etc/hostname</strong> and doesn't available to DNS server in our intranet...</p>
  
  <p>Any other way to retrieve IP of open session to Oracle DB (DB instance hold information about its sockets in any case...).</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I under trusted intranet but with unknown network hierarchy.</p>
  
  <p>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...</p>