Get the host name and IP address of an Oracle database from sqlplus

A. Through UTL_INADDR

Starting from Oracle 8.1.6, the UTL_INADDR package has been introduced for retrieving host names and IP addresses from PL/SQL. It has two functions: GET_HOST_ADDRESS for querying IP address and GET_HOST_NAME for host name.

SQL> desc UTL_INADDR

FUNCTION GET_HOST_ADDRESS RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HOST                           VARCHAR2                IN     DEFAULT

FUNCTION GET_HOST_NAME RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 IP                             VARCHAR2                IN     DEFAULT

1. Get the host name.

SQL> SELECT UTL_INADDR.get_host_name FROM dual;

GET_HOST_NAME
----------------------------------------
GreatPower

If you know the IP address of the database, you can use that in the query.

SQL> SELECT UTL_INADDR.get_host_name('119.118.1.001') FROM dual;

GET_HOST_NAME('119.118.1.001')
------------------------------------------------
GreatPower

2. Get the IP address

SQL> SELECT UTL_INADDR.get_host_address from dual;

UTL_INADDR.GET_HOST_ADDRESS
------------------------------------------------------------------------
119.118.1.001

If you know the database's host name, you can also use that in the query.

SQL> SELECT UTL_INADDR.get_host_address('GreatPower') from dual;

UTL_INADDR.GET_HOST_ADDRESS('GreatPower')
------------------------------------------------------------------------
119.118.1.001

B. Through SYS_CONTEXT

SQL> SELECT SYS_CONTEXT('USERENV','HOST') as host FROM dual;

HOST
--------------------------------------------------
GreatPower

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

IP
--------------------------------------------------------------
119.118.1.001

C. V$INSTANCE

SQL> SELECT host_name FROM v$instance;

HOST_NAME

----------------------------------------------------------------

GreatPower

-----------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.