Get IP address of oracle client users

Question

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?

Answer 1

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

SQL>

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;

Answer 2

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;