Find server IP using SQL Query

Hello, friends in this article we going to discuss how to find server IP or Hostname using SQL query.

How to check hostname using SQL query in oracle?

The following query will show the hostname where you’re currently connected in that session.

SELECT  host_name FROM v$instance;

The above query will work only for a single instance, if you are connected on multi-instances then use the following query to check all the hostnames.

SELECT  host_name FROM gv$instance;

In case, If you don’t have access to the v$ views, there are two alternatives. You can get it from the dual table using the below queries.

select utl_inaddr.get_host_name from dual; 

 OR 

select sys_context('USERENV','SERVER_HOST') from dual;  

sys_context() as the easiest and most appropriate solution. The only reason not to use sys_context is that it lower cases the name, which the above.

Find server IP using SQL

You can use sys_context(‘USERENV‘, ‘SERVICE_NAME‘) to get the database service name instead of the machine name. Use the below query to find complete details.

select sys_context ( 'USERENV', 'DB_NAME' ) db_name,
          sys_context ( 'USERENV', 'SESSION_USER' ) user_name,
          sys_context ( 'USERENV', 'SERVER_HOST' ) db_host,
          sys_context ( 'USERENV', 'HOST' ) user_host
         from dual;

Read - How to Configure Oracle Network Environment

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology