Identifying Host Names
and IP Addresses
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
The UTL_INADDR package was introduced in Oracle 8.1.6 to 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('bart')
FROM dual;
UTL_INADDR.GET_HOST_ADDRESS('BART')
--------------------------------------------------------------------------------
192.168.2.4
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.168.2.5
SQL>
An error is returned if the specified host name
is not recognized.
SQL> SELECT * UTL_INADDR.get_host_address('banana')
from dual;
SELECT * UTL_INADDR.get_host_address('banana') from
dual
ERROR at line 1:
ORA-29257: host banana unknown
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1
SQL>
The GET_HOST_ADDRESS function returns the host name of the specified IP address.
SQL> SELECT
UTL_INADDR.get_host_name('192.168.2.4') FROM dual;
UTL_INADDR.GET_HOST_NAME('192.168.2.4')
--------------------------------------------------------------------------------
bart
SQL>
The host name of the database server is returned
if the specified IP address is NULL or omitted.
SQL> SELECT UTL_INADDR.get_host_name FROM dual;
GET_HOST_NAME
--------------------------------------------------------------------------------
C4210gR2
1 row selected.
SQL>
An error is returned if the specified IP address
is not recognized.
SQL> SELECT * UTL_INADDR.get_host_name('1.1.1.1')
FROM dual;
SELECT * UTL_INADDR.get_host_name('1.1.1.1') FROM
dual;
ERROR at line 1:
ORA-29257: host 1.1.1.1 unknown
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1
SQL>
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.
The following examples show the typical output
for each variant.
SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL')
FROM dual;
SYS_CONTEXT('USERENV','TERMINAL')
--------------------------------------------------------------------
marge
1 row selected.
SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM
dual;
SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------
marge
1 row selected.
SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS')
FROM dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------
192.168.2.3
1 row selected.
SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST')
FROM dual;
SYS_CONTEXT('USERENV','SERVER_HOST')
--------------------------------------------------------------------
C4210gr2
1 row selected.
SQL>
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
------------------------------------------------
C4210gR2
1 row selected.
SQL>
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 = 'TIM_HALL';
TERMINAL MACHINE
------------------------------
----------------------------------------------------
MARGE ORACLE-BASE\MARGE
1 row selected.
SQL>
