Check the DB or OS User, IP address, hostname, service name from session in Oracle

Check the DB User, OS User, IP address, hostname, service name from session in Oracle

Check the OS User from session

--- From sys_context
SELECT SYS_CONTEXT ('USERENV', 'OS_USER') FROM DUAL;
SYS_CONTEXT('USERENV','OS_USER')
--------------------------------
Oracle


Check the current user session

SQL> conn test@xepdb1
Enter password:
Connected.
SQL>
SQL> SELECT SYS_CONTEXT ('USERENV','SESSION_USER') FROM DUAL;

SYS_CONTEXT('USERENV','SESSION_USER')
-------------------------------------
TEST


Check service name which Session connected

SQL> SELECT SYS_CONTEXT ('USERENV','SERVICE_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','SERVICE_NAME')
-------------------------------------
xepdb1


Get the hostname from SQLPLUS

SQL> SELECT SYS_CONTEXT ('USERENV','SERVER_HOST') FROM DUAL;

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


Get the IP ADDRESS from session connected

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

SYS_CONTEXT('USERENV','IP_ADDRESS')
-----------------------------------
10.10.238.210


Author: SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator. View all posts by SandeepSingh