How to use SYS_CONTEXT to display user information

Oracle provides a built-in namespace called USERENV, which describes the current session.

The function SYS_CONTEXT can be used to return the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

You can use the SYS_CONTEXT to retreive userinformation from the namespace USERENV, some examples below:

SELECT SYS_CONTEXT ('userenv','OS_USER') "OS user", 
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','IDENTIFICATION_TYPE') "Identification type",
       SYS_CONTEXT('userenv','IP_ADDRESS') "IP",
       SYS_CONTEXT('userenv','HOST') "Host name",
       SYS_CONTEXT('userenv','SID') "SID",
       SYS_CONTEXT('userenv','SERVICE_NAME') "Service"
FROM DUAL;

Executed from a remote session, logged into the database as user SCOTT, the output from the query above will be:

OS user	Current schema	Identification type	IP	Host name	SID	Service
SCOTT	SCOTT	LOCAL	192.168.1.2	MYDOMAIN\MYCLIENT	170	myservice.mydomain.com

To find the serial# of your session, you will need to query the v$session dynamic performance view. you need to have SELECT privileges on the V$SESSION view to use this query, so first, a grant is needed.

SQL> show user
USER is "SYS"

GRANT SELECT ON V_$SESSION TO SCOTT;

Now, as user SCOTT, you can execute the query

SELECT SID, SERIAL#
FROM V$SESSION
WHERE SID=SYS_CONTEXT('USERENV','SID');


Source: Oracle Documentation The 12.2 SYS_CONTEXT documentation can be found here