How to find Client IP address by Quering Oracle Database ?

We can use PLSQL package UTL_INADDR.

There are two procedures'

1. GET_HOST_NAME or
2. GET_HOST_ADDRESS

for finding out local machine HOST name or IP address.

UTL_INADDR

We can use below query to findout CLIENT IP ADDRESS.

SQL> col machine for a20
SQL> col UTL_INADDR.GET_HOST_ADDRESS(MACHINE) for a40

SQL> select sid,machine,UTL_INADDR.GET_HOST_ADDRESS (machine)
from v$session
where type = 'USER' and username is not null
order by sid;

SID MACHINE UTL_INADDR.GET_HOST_ADDRESS(MACHINE)
---------- -------------------- ----------------------------------------
152 edrserver 10.21.1.249

=====================================================
If you are getting below error message then modify above code little bit.

ERROR at line 1:ORA-29257: host xxxxxxxxxxxxxxxxxx unknown

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at line 1

==================Modified code======================

SQL> select sid, machine,
UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1)) ip
from v$session
where type='USER' and username is not null
order by sid;

SID MACHINE IP
---------- -------------------- ------------------------------
152 edrserver 10.21.1.249