5

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?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
user2470764
  • 51
  • 1
  • 2
  • 8
  • Is this a client/server application? Or is this a three-tier application where the client machine from the database's perspective is a middle tier application server? – Justin Cave Mar 04 '15 at 05:19
  • what do you mean by _with any information of the session like the consumption of the session?_ – Sathyajith Bhat Mar 04 '15 at 05:25
  • Possible duplicate of [Get IP addresses of established connections to Oracle 11](https://stackoverflow.com/questions/15069145/get-ip-addresses-of-established-connections-to-oracle-11) – T-Gergely Nov 09 '17 at 13:20

2 Answers2

12

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;

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 1
    This returns YOUR IP Address but the question was how to get the IP of all other users. – Wernfried Domscheit Mar 04 '15 at 06:23
  • Here the context needs to be set for *YOUR*. i understand what your are saying, for all users from v$session we could get the host address via machine name. The same machine name is synonymous with host_name in v$instance. Usually we do `utl_inaddr.get_host_address(host_name)` from v$instance. So, from v$session, we could use machine. Will edit my answer. – Lalit Kumar B Mar 04 '15 at 09:02
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;
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110