SYS_CONTEXT fonksiyonunu veri tabanına bağlı bulunduğumuz kullanıcı üzerinde set edilmiş sistem değişkenlerini(enviroment) toplamak için kullanabiliriz. Bu sayede kullanıcı üzerine atadığımız ya da otomatik olarak atanan tüm değerlere ulaşabiliriz.
Özellikle yazılım için de log mekanizması veya trigger larda çok fazla kullanıldığı gibi çokda iş görüyor.
Bu fonksiyon ile database üzerinden birçok veriye ulaşabiliriz. Kullanıcı bilgisi, ip bilgisi, tarih formatı, dil bilgisi vb.Örnegin; Veritabanı sid bilgisine ulaşmak için;
select sys_context('userenv','sid') from dual;
veya veritabanı ismine ulaşmak için aşagıdaki sql’den yararlanabiliriz.
select sys_context('userenv','db_name') from dual;
SYS_CONTEXT ile kullanabilecegimizparametrelerden bazıları aşagıda ki gibidir.
- ACTION
- AUDITED_CURSORID
- AUTHENTICATED_IDENTITY
- AUTHENTICATION_DATA
- AUTHENTICATION_METHOD
- BG_JOB_ID
- CLIENT_IDENTIFIER
- CLIENT_INFO
- CURRENT_BIND
- CURRENT_EDITION_ID
- CURRENT_EDITION_NAME
- CURRENT_SCHEMA
- CURRENT_SCHEMAID
- CURRENT_SQL
- CURRENT_SQL n
- CURRENT_SQL_LENGTH
CURRENT_USER - CURRENT_USERID
- DATABASE_ROLE
- DB_DOMAIN
- DB_NAME
- DB_UNIQUE_NAME
- DBLINK_INFO
- ENTRYID
- ENTERPRISE_IDENTITY
- FG_JOB_ID
- GLOBAL_CONTEXT_MEMORY
- GLOBAL_UID
- HOST
- IDENTIFICATION_TYPE
- INSTANCE
- INSTANCE_NAME
- IP_ADDRESS
- ISDBA
- LANG
- LANGUAGE
- MODULE
- NETWORK_PROTOCOL
- NLS_CALENDAR
- NLS_CURRENCY
- NLS_DATE_FORMAT
- NLS_DATE_LANGUAGE
- NLS_SORT
- NLS_TERRITORY
- OS_USER
- POLICY_INVOKER
- PROXY_ENTERPRISE_IDENTITY
- PROXY_USER
- PROXY_USERID
- SERVER_HOST
- SERVICE_NAME
- SESSION_EDITION_ID
- SESSION_EDITION_NAME
- SESSION_USER
- SESSION_USERID
- SESSIONID
- SID
- STATEMENTID
- TERMINAL
Aşagıda ki sql ler yardımıyla tüm enviroment bilgilerine erişilebilir.
SELECT (SELECT SYS_CONTEXT ('userenv', 'SID') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'CURRENT_SCHEMA') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'CURRENT_USER') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'INSTANCE_NAME') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'IP_ADDRESS') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'CURRENT_SQL') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'MODULE') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'TERMINAL') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'OS_USER') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'HOST') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'LANG') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'LANGUAGE') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'NLS_CALENDAR') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'NLS_CURRENCY') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'NLS_DATE_FORMAT') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'NLS_DATE_LANGUAGE') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'NLS_SORT') FROM DUAL), (SELECT SYS_CONTEXT ('userenv', 'NLS_TERRITORY') FROM DUAL) FROM DUAL;
veya aşağıdakini kullanabiliriz;
select res.* from ( select * from ( select sys_context ('userenv','ACTION') ACTION, sys_context ('userenv','AUDITED_CURSORID') AUDITED_CURSORID, sys_context ('userenv','AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY, sys_context ('userenv','AUTHENTICATION_DATA') AUTHENTICATION_DATA, sys_context ('userenv','AUTHENTICATION_METHOD') AUTHENTICATION_METHOD, sys_context ('userenv','BG_JOB_ID') BG_JOB_ID, sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER, sys_context ('userenv','CLIENT_INFO') CLIENT_INFO, sys_context ('userenv','CURRENT_BIND') CURRENT_BIND, sys_context ('userenv','CURRENT_EDITION_ID') CURRENT_EDITION_ID, sys_context ('userenv','CURRENT_EDITION_NAME') CURRENT_EDITION_NAME, sys_context ('userenv','CURRENT_SCHEMA') CURRENT_SCHEMA, sys_context ('userenv','CURRENT_SCHEMAID') CURRENT_SCHEMAID, sys_context ('userenv','CURRENT_SQL') CURRENT_SQL, sys_context ('userenv','CURRENT_SQLn') CURRENT_SQLn, sys_context ('userenv','CURRENT_SQL_LENGTH') CURRENT_SQL_LENGTH, sys_context ('userenv','CURRENT_USER') CURRENT_USER, sys_context ('userenv','CURRENT_USERID') CURRENT_USERID, sys_context ('userenv','DATABASE_ROLE') DATABASE_ROLE, sys_context ('userenv','DB_DOMAIN') DB_DOMAIN, sys_context ('userenv','DB_NAME') DB_NAME, sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME, sys_context ('userenv','DBLINK_INFO') DBLINK_INFO, sys_context ('userenv','ENTRYID') ENTRYID, sys_context ('userenv','ENTERPRISE_IDENTITY') ENTERPRISE_IDENTITY, sys_context ('userenv','FG_JOB_ID') FG_JOB_ID, sys_context ('userenv','GLOBAL_CONTEXT_MEMORY') GLOBAL_CONTEXT_MEMORY, sys_context ('userenv','GLOBAL_UID') GLOBAL_UID, sys_context ('userenv','HOST') HOST, sys_context ('userenv','IDENTIFICATION_TYPE') IDENTIFICATION_TYPE, sys_context ('userenv','INSTANCE') INSTANCE, sys_context ('userenv','INSTANCE_NAME') INSTANCE_NAME, sys_context ('userenv','IP_ADDRESS') IP_ADDRESS, sys_context ('userenv','ISDBA') ISDBA, sys_context ('userenv','LANG') LANG, sys_context ('userenv','LANGUAGE') LANGUAGE, sys_context ('userenv','MODULE') MODULE, sys_context ('userenv','NETWORK_PROTOCOL') NETWORK_PROTOCOL, sys_context ('userenv','NLS_CALENDAR') NLS_CALENDAR, sys_context ('userenv','NLS_CURRENCY') NLS_CURRENCY, 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','NLS_TERRITORY') NLS_TERRITORY, sys_context ('userenv','OS_USER') OS_USER, sys_context ('userenv','POLICY_INVOKER') POLICY_INVOKER, sys_context ('userenv','PROXY_ENTERPRISE_IDENTITY') PROXY_ENTERPRISE_IDENTITY, sys_context ('userenv','PROXY_USER') PROXY_USER, sys_context ('userenv','PROXY_USERID') PROXY_USERID, sys_context ('userenv','SERVER_HOST') SERVER_HOST, sys_context ('userenv','SERVICE_NAME') SERVICE_NAME, sys_context ('userenv','SESSION_EDITION_ID') SESSION_EDITION_ID, sys_context ('userenv','SESSION_EDITION_NAME') SESSION_EDITION_NAME, sys_context ('userenv','SESSION_USER') SESSION_USER, sys_context ('userenv','SESSION_USERID') SESSION_USERID, sys_context ('userenv','SESSIONID') SESSIONID, sys_context ('userenv','SID') SID, sys_context ('userenv','STATEMENTID') STATEMENTID, sys_context ('userenv','TERMINAL') TERMINAL from dual -- where sys_context ('userenv','SESSIONID') NOT in ('SYS', 'XDB') -- <<<<< filter by user ) unpivot include nulls ( val for name in (action, audited_cursorid, authenticated_identity, authentication_data, authentication_method, bg_job_id, client_identifier, client_info, current_bind, current_edition_id, current_edition_name, current_schema, current_schemaid, current_sql, current_sqln, current_sql_length, current_user, current_userid, database_role, db_domain, db_name, db_unique_name, dblink_info, entryid, enterprise_identity, fg_job_id, global_context_memory, global_uid, host, identification_type, instance, instance_name, ip_address, isdba, lang, language, module, network_protocol, nls_calendar, nls_currency, nls_date_format, nls_date_language, nls_sort, nls_territory, os_user, policy_invoker, proxy_enterprise_identity, proxy_user, proxy_userid, server_host, service_name, session_edition_id, session_edition_name, session_user, session_userid, sessionid, sid, statementid, terminal) ) ) res;
Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe