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;
/
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment