반응형

# 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


반응형
저작자표시 (새창열림)

'Database > ORACLE' 카테고리의 다른 글

오라클 undo / Pending Offline Segment  (0) 2018.01.04
오라클 11g 자동 통계 정보 수집 내용 변경  (0) 2017.12.30
inactive and locked 세션 관련  (1) 2017.09.25
SQL 튜닝의 시작 사용 SQL 스크립트  (0) 2017.09.17
ORA-27086 Linux-x86_64 Error 37 : No Locks available  (0) 2017.07.27

+ Recent posts

티스토리툴바