Sql Query to Get Hostname, IP Address and Terminal from Oracle Database

If your user doesn't have DBA privilege then you can use SYS_CONTEXT function to get the hostname and IP address.

Option a. sys_context
SQL> SELECT SYS_CONTEXT
('USERENV', 'SERVER_HOST') from dual;

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

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

SYS_CONTEXT('USERENV','HOST')
---------------------------------------
proddb001.history.local

Without DBA privilege it will not work. You can access v$instance 
data dictionary view with DBA privilege only.
Option b. v$instance
SQL> select host_name from v$instance;
select host_name from v$instance
                      *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn / as sysdba
Connected.
SQL> select host_name from v$instance
SQL> /

HOST_NAME
---------------------------------------
proddb001.history.local
Option c. UTL_INADDER.get_host_name
SQL> SELECT UTL_INADDR.get_host_name('20.228.10.32' ) FROM dual;

UTL_INADDR.GET_HOST_NAME('20.228.10.32')
---------------------------------------
PRODDB001.history.local

To Get IP address

Option a. UTL_INADDR.get_host_address
SQL> SELECT UTL_INADDR.get_host_address from dual;

GET_HOST_ADDRESS
---------------------------------------
20.228.10.32


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

UTL_INADDR.GET_HOST_ADDRESS('PRODDB001')
---------------------------------------
20.228.10.32

Query to find out TERMINAL
SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

SYS_CONTEXT('USERENV','TERMINAL')
---------------------------------------
pts/4
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google