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

advertisements

_____________________________________________________________________________________________________________________

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

_____________________________________________________________________________________________________________________

Website Stats