How to extract current Oracle user name and schema name?

Issue following query to extract current user name ( Session name ):

Syntax:
SELECT
sys_context(‘USERENV’,’SESSION_USER’) as “USER NAME”, 
sys_context(‘USERENV’, ‘CURRENT_SCHEMA’) as “CURRENT SCHEMA” 
FROM dual;

sys_context() : This function returns the value of parameter associated with the context namespace.

USERENV : This is an Oracle provided namespace that describes the current session.

 

Issue following query to extract schema name:
show user;

Example:
column “USER NAME” format a15
column “CURRENT SCHEMA” format a15
SELECT
sys_context(‘USERENV’,’SESSION_USER’) as “USER NAME”,
sys_context(‘USERENV’, ‘CURRENT_SCHEMA’) as “CURRENT SCHEMA”
FROM dual;

USER NAME CURRENT SCHEMA

————— —————
SYS             SYS

show user;
USER is “SYS”

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply