Get IP addresses of established connections to Oracle 11

Question

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.

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';

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

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...

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

UPDATE

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...

Answer 1

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.

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

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.

Answer 2

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.

For this tasks you can use general purpose utilities like netstat or lsof -p <pid-of-oracle> but only on server side!

Some help can come from v$session.port values...

One good suggestion from helpers - use good names from DB clients. They are populated to v$session table rows:

machine, terminal, program, module, service_name

so they can help to identify clients...

Answer 3

Try this in your select statements (i.e., from v$session):

utl_inaddr.get_host_address(substr(machine,instr(machine,'\')+1)) ip

Sorry, I don't have the source, but it worked for me.

Answer 4

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.

Answer 5

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.