Saturday, September 26, 2009

user connection details in oracle with SYS_CONTEXT

SET serveroutput on size 500000
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;
/

No comments: