Use Sys_context To Obtain Session Information

Use SYS_CONTEXT to Obtain Session Information This tip comes from Ahbaid Gaffoor, DBA in Dallas, TX. Use the SYS_CONTEXT call to extract session infomation for your application. Here's a sample script which I use frequently to obtain a session's information when connected in SQL*Plus: --- Author: -- Date: -- File: --- Purpose: connection details --- Usage: ---

Ahbaid Gaffoor Wednesday 18th December 2002 context.sql This script uses the SYS_CONTEXT function to retrieve user @context (at SQL prompt)

set serveroutput on size 400000 set feedback off declare cursor c_ctx is select SYS_CONTEXT('USERENV','TERMINAL') terminal, SYS_CONTEXT('USERENV','LANGUAGE') language, SYS_CONTEXT('USERENV','SESSIONID') sessionid, SYS_CONTEXT('USERENV','INSTANCE') instance, SYS_CONTEXT('USERENV','ENTRYID') entryid, SYS_CONTEXT('USERENV','ISDBA') isdba, SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory, SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency, SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language, SYS_CONTEXT('USERENV','NLS_SORT') nls_sort, SYS_CONTEXT('USERENV','CURRENT_USER') current_user, SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid, SYS_CONTEXT('USERENV','SESSION_USER') session_user, SYS_CONTEXT('USERENV','SESSION_USERID') session_userid, SYS_CONTEXT('USERENV','PROXY_USER') proxy_user, SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid, SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain, SYS_CONTEXT('USERENV','DB_NAME') db_name, SYS_CONTEXT('USERENV','HOST') host, SYS_CONTEXT('USERENV','OS_USER') os_user, SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name, SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol, SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id, SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data, SYS_CONTEXT('USERENV','CURRENT_SQL') current_sql,

SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') client_identifier, SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') global_context_memory from dual; ctx c_ctx%ROWTYPE; --- Utility Functions -function pad (s varchar2, padsize number := 25, padchar varchar2 := ' ') return varchar2 is begin return rpad(s,padsize,padchar); end pad; procedure print(s varchar2) is begin dbms_output.put_line(s); end print; procedure newln is begin print(chr(10)); end newln; begin open c_ctx; fetch c_ctx into ctx; close c_ctx; newln; print('Connection Context'); print('=================='); newln; print('User Details'); print('------------'); print(pad('Session ID: ')||ctx.sessionid); print(pad('Session User ID: ')||ctx.Session_userid||' / '||ctx.session_user); print(pad('Current User ID: ')||ctx.current_userid||' / '||ctx.current_user); print(pad('Is DBA: ')||ctx.isdba); newln; print('Machine/Network Details'); print('-----------------------'); print(pad('Terminal: ')||ctx.terminal); print(pad('Host: ')||ctx.host); print(pad('OS User: ')||ctx.os_user); print(pad('IP Address: ')||ctx.ip_address); print(pad('Network Protocol: ')||ctx.network_protocol); print(pad('Authentication Type: ')||ctx.authentication_type); newln; print('NLS Settings'); print('------------'); print(pad('NLS Territory: ')||ctx.nls_territory); print(pad('NLS Currency: ')||ctx.nls_currency); print(pad('NLS Date Format: ')||ctx.nls_date_format); print(pad('NLS Date Language: ')||ctx.nls_date_language); print(pad('NLS Sort: ')||ctx.nls_sort);

newln; print('Database'); print('--------'); print(pad('DB Name: ')||ctx.db_name); print(pad('DB Domain: ')||ctx.db_domain); print(pad('Language: ')||ctx.language); newln; end; / set feedback on

Here's some sample output from the script, of course you can modify it to suit your own needs. @context Connection Context ================== User Details -----------Session ID: Session User ID: Current User ID: Is DBA:

553578 5 / SYSTEM 5 / SYSTEM FALSE

Machine/Network Details ----------------------Terminal: Host: OS User: IP Address: Network Protocol: Authentication Type:

GUYANA SAMERICA\GUYANA Administrator 10.36.21.110 tcp DATABASE

NLS Settings -----------NLS Territory: NLS Currency: NLS Date Format: NLS Date Language: NLS Sort:

AMERICA $ DD-MON-RR AMERICAN BINARY

Database -------DB Name: DB Domain: Language:

TOTAL SAMERICA.COM AMERICAN_AMERICA.US7ASCII