Search This Blog

Sunday, March 30, 2008

Client IP Address From Server.

How to find Client IP address from Oracle Server?

We can use PLSQL package UTL_INADDR.

There is two procedure 1. GET_HOST_NAME or 2. GET_HOST_ADDRESS for find out local or remote HOST name or IP address.

UTL_INADDR

We can use below query to findout CLIENT IP ADDRESS.

select sid,machine,UTL_INADDR.GET_HOST_ADDRESS (machine)

from v$session

where type = 'USER' and username is not null

order by sid;

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

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;


1 comment:

Mirza's said...

its giving error too

ORA-29257: host GIL-DBA unknown
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1