Get IP Address from hostname within Oracle Database

We everyone know that with ping command we can get IP Address of another computer.
Suppose from terminal on Solaris machine,use
ping -s hostname to get IP Address of the host.

From Terminal of Linux Machine, use
ping hostname to get IP Address of the host.

But within oracle database how we can convert hostname to IP address?

It can be done by UTL_INADDR package. Within this package GET_HOST_ADDRESS subprograms takes a varchar2 datatype within which we can give the host name for which we want to get IP.

For example, to get IP address of computer saturn, use


SQL> select UTL_INADDR.GET_HOST_ADDRESS('saturn') from dual;

UTL_INADDR.GET_HOST_ADDRESS('SATURN')
--------------------------------------------------------------------------------
192.168.1.11


We can easily use it to see which IP address is now connected to oracle database. Like,
COL machine format a30
COL ip format a20
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;

or,

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

While issuing this query you may get error ORA-29257, ORA-06512 like

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

For which host you get just exclude that in predicate. Like,

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 and machine not in ('quark.arjubd.com', 'nilanjona','sinewave','loris.arjubd.com','appdemo','nilanjona.arjubd.com');
Related Documents
How to find the User who is connected to Oracle
How to find current session ID
How to know which objects are being accessed by a user