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
No comments:
Post a Comment