Oracle SYS_CONTEXT Function

  How to check the database/instance name without having access to data dictionary views? How to get the client machine details from which I have connected to the Oracle database? How can I know my session details?

These questions might have come up in the mind of many oracle database users with limited privileges.

Well. SYS_CONTEXT Function is the answer for all these questions.

Lets understand more on this with the below given examples.

>> To check the Database name

SQL> SELECT sys_context(‘USERENV’, ‘DB_NAME’) FROM DUAL; 

SYS_CONTEXT(‘USERENV’,’DB_NAME’)
——————————————————–
TESTDB

 

>> To check the Instance Name

SQL> select SYS_CONTEXT( ‘USERENV’, ‘INSTANCE_NAME’) from dual;

 SYS_CONTEXT(‘USERENV’,’INSTANCE_NAME’)
—————————————————————
TESTDB2

 

>> To check the Database Server name

SQL> SELECT SYS_CONTEXT(‘USERENV’,’SERVER_HOST’) FROM dual;

SYS_CONTEXT(‘USERENV’,’SERVER_HOST’)
————————————————————
spqmd112

 

> > To check the client machine name

SQL> SELECT SYS_CONTEXT(‘USERENV’,’TERMINAL’) FROM dual; 

SYS_CONTEXT(‘USERENV’,’TERMINAL’)
——————————————————————
01HW311024

 

>> To check the ip address of the Client Machine

SQL> SELECT SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’) FROM dual;

SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’)
——————————————————————
158.223.54.68

 

>> To check the OS user

SQL> select SYS_CONTEXT( ‘USERENV’, ‘OS_USER’ ) os_user from dual; 

OS_USER
———————————————————–
testuser12

 

>> To check the Session ID

SQL> select SYS_CONTEXT( ‘USERENV’, ‘SID’) from dual; 

SYS_CONTEXT(‘USERENV’,’SID’)
——————————————————–
196

 

Article by : Anju