Oracle - SYS_CONTEXT 함수를 이용하여 접속 세션 정보 추출

# INSTANCE NAME/ DB NAME을 확인하고 싶은데 권한이 없는 일반 유저가 확인하고 싶을때 가능
 - SYS_CONTEXT 함수는 세션정보를 얻어오는 함수 입니다.
 - USERENV : 현재 세션의 환경정보를 반환는 네임스페이스 입니다.
   SYS_CONTEXT ('namespace', 'parameter')

Oracle Docs : http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions079.htm

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

-- DB 이름 SELECT SYS_CONTEXT('userenv', 'db_name') db_name FROM DUAL; DB_NAME ------------ oracle3   -- Instance 이름 SELECT SYS_CONTEXT('userenv', 'instance_name') instance_name FROM DUAL; INSTANCE_NAME ------------------ oraSub3   -- 접속자 IP 주소 SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') "My IP Address"  FROM DUAL;   -- DBA 여부 SELECT SYS_CONTEXT('USERENV','ISDBA') isdba FROM DUAL; ISDBA ------- TRUE     SELECT SYS_CONTEXT('USERENV','TERMINAL') terminal FROM DUAL; SELECT SYS_CONTEXT('USERENV','LANGUAGE') language FROM DUAL; SELECT SYS_CONTEXT('USERENV','SESSIONID') sessionid FROM DUAL; SELECT SYS_CONTEXT('USERENV','INSTANCE') instance FROM DUAL; SELECT SYS_CONTEXT('USERENV','ENTRYID') entryid FROM DUAL; SELECT SYS_CONTEXT('USERENV','ISDBA') isdba FROM DUAL; SELECT SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory FROM DUAL; SELECT SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency FROM DUAL; SELECT SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar FROM DUAL; SELECT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format FROM DUAL; SELECT SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language FROM DUAL; SELECT SYS_CONTEXT('USERENV','NLS_SORT') nls_sort FROM DUAL; SELECT SYS_CONTEXT('USERENV','CURRENT_USER') current_user FROM DUAL; SELECT SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid FROM DUAL; SELECT SYS_CONTEXT('USERENV','SESSION_USER') session_user FROM DUAL; SELECT SYS_CONTEXT('USERENV','SESSION_USERID') session_userid FROM DUAL; SELECT SYS_CONTEXT('USERENV','PROXY_USER') proxy_user FROM DUAL; SELECT SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid FROM DUAL; SELECT SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain FROM DUAL; SELECT SYS_CONTEXT('USERENV','DB_NAME') db_name FROM DUAL; SELECT SYS_CONTEXT('USERENV','HOST') host FROM DUAL; SELECT SYS_CONTEXT('USERENV','OS_USER') os_user FROM DUAL; SELECT SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name FROM DUAL; SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address FROM DUAL; SELECT SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol FROM DUAL; SELECT SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id FROM DUAL; SELECT SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id FROM DUAL; SELECT SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') FROM DUAL; 출처 : http://develop.sunshiny.co.kr/681