Get IP address of oracle client users

I need a query to get all IP's of client users that have already a session to the database

You could use SYS_CONTEXT. It would return the following host and IP address information for the current session:

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

Have a look at this article by Tim Hall.

For example,

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;SYS_CONTEXT('USERENV','IP_ADDRESS')----------------------------------------------------------127.0.0.1SQL>

For me the IP is localhost, so I get 127.0.0.1

Edit From discussions in the comments below, to get the list of the IP address of all the users from v$session, you could use MACHINE they are connected from.

SELECT utl_inaddr.get_host_address(t.machine), t.* FROM v$session t;

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;