How to Get Oracle Database IP?

Question

How can I get the IP Address of an Oracle database? I'm not the Administrator.

I'm using an Oracle application that has the access to that database. How can I get the IP Address of the database?

Answer 1

From within SQL*Plus (Requires specific access rights to the network related functions, so that might or might not be available for you as a non-administrator):

SQL>  connect / as sysdba;
Connected.
SQL> SELECT UTL_INADDR.get_host_address from dual;

GET_HOST_ADDRESS
--------------------------------------------------------------------------------
192.168.1.42

See also the UTL_INADDR documentation.

From the shell (if you are using tnsnames for resolving database names):

$ tnsping InstanceID
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=host.dbserver.com)(PORT=1521)) (CONNECT_DATA= (SID=ORCL)))
                                                                 ^^^^^^^^^^^^^^^^^

Note the HOST output - this is the host name of your database server.

If your application is using JDBC to access the database, the host name is already part of the JDBC connection string:

jdbc:oracle:thin:@host.dbserver.com:1521:ORCL
                  ^^^^^^^^^^^^^^^^^

In either case, you can then use ping to get the IP address:

$ ping host.dbserver.com
PING host.dbserver.com (192.168.1.42) 56(84) bytes of data.
                        ^^^^^^^^^^^^

Answer 2

There are a few ways. Using sys_context:

SELECT sys_context('USERENV','IP_ADDRESS') FROM DUAL;

Or with sys.utl_inaddr:

SELECT utl_inaddr.get_host_address FROM DUAL;

Or the same commands using PL/SQL:

SET serveroutput ON
BEGIN
  dbms_output.put_line(utl_inaddr.get_host_address);
  dbms_output.put_line(sys_context('USERENV','IP_ADDRESS'));
END;
/