How to Get IP address and host address in Oracle

This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.

UTL_INADDR
SYS_CONTEXT
V$INSTANCE
V$SESSION

UTL_INADDR

The UTL_INADDR provide a means of retrieving host names and IP addresses of remote hosts from PL/SQL.

The GET_HOST_ADDRESS function returns the IP address of the specified host name.

SQL> SELECT UTL_INADDR.get_host_address('TEST') FROM dual;

UTL_INADDR.GET_HOST_ADDRESS('TEST')
--------------------------------------------------------------------------------
192.167.1.56

SQL>

 The IP address of the database server is returned if the specified host name is NULL or is omitted.

SQL> SELECT UTL_INADDR.get_host_address from dual;

GET_HOST_ADDRESS
--------------------------------------------------------------------------------
192.161.1.55

SYS_CONTEXT

The SYS_CONTEXT function is able to 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.

SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

SYS_CONTEXT('USERENV','TERMINAL')
--------------------------------------------------------------------
TEST10

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------
192.167.1.55

SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;

SYS_CONTEXT('USERENV','SERVER_HOST')
--------------------------------------------------------------------
Z4210gr11

V$INSTANCE

The HOST_NAME column of the V$INSTANCE view contains the host name of the server running the instance.

SQL> SELECT host_name FROM v$instance;

HOST_NAME
------------------------------------------------
Z4210gR11

V$SESSION

The V$SESSION view contains the following host information for all database sessions:

TERMINAL – The operating system terminal name for the client. This is often set to the client machine name.

MACHINE – The operating system name for the client machine. This may include the domain name if present.

The following examples show the typical output for each column.

SQL> SELECT terminal, machine FROM v$session WHERE username = 'OEAG';

TERMINAL                       MACHINE
------------------------------ ----------------------------------------------------
TEST10                          ORACLE-BASETEST10