【DB筆試面試500】在Oracle中，如何獲取IP地址？

• 2019 年 10 月 11 日
• 筆記

在Oracle中，如何獲取IP地址？

答案部分

若是獲取伺服器IP地址，則使用UTL_INADDR.GET_HOST_ADDRESS。若是獲取客戶端IP地址則使用SYS_CONTEXT('USERENV','IP_ADDRESS')。

SYS_CONTEXT函數是Oracle提供的一個獲取環境上下文資訊的預定義函數。該函數用來返回一個指定NAMESPACE下的PARAMETER值。該函數可以在SQL和PL/SQL語言中使用。

SELECT SYS_CONTEXT('USERENV', 'ACTION') ACTION,         SYS_CONTEXT('USERENV', 'AUDITED_CURSORID') AUDITED_CURSORID,         SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,         SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') AUTHENTICATION_TYPE,         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, --USERENV('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, --USERENV('ENTRYID')         SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY') ENTERPRISE_IDENTITY,         SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') EXTERNAL_NAME,         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, -- USERENV('TERMINAL')         SYS_CONTEXT('USERENV', 'IDENTIFICATION_TYPE') IDENTIFICATION_TYPE,         SYS_CONTEXT('USERENV', 'INSTANCE') INSTANCE, --USERENV('INSTANCE')         SYS_CONTEXT('USERENV', 'INSTANCE_NAME') INSTANCE_NAME,         SYS_CONTEXT('USERENV', 'IP_ADDRESS') IP_ADDRESS, --ORA_CLIENT_IP_ADDRESS         SYS_CONTEXT('USERENV', 'ISDBA') ISDBA,  --USERENV('ISDBA')         SYS_CONTEXT('USERENV', 'LANG') LANG, --USERENV('LANG')         SYS_CONTEXT('USERENV', 'LANGUAGE') LANGUAGE, --USERENV('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, --ORA_LOGIN_USER         SYS_CONTEXT('USERENV', 'SESSION_USERID') SESSION_USERID,         SYS_CONTEXT('USERENV', 'SESSIONID') SESSIONID, --  USERENV('SESSIONID') , V$SESSION.AUDSID         SYS_CONTEXT('USERENV', 'SID') SID,         SYS_CONTEXT('USERENV', 'STATEMENTID') STATEMENTID,         SYS_CONTEXT('USERENV', 'TERMINAL') TERMINAL   --USERENV('TERMINAL')    FROM DUAL;  

USERENV函數用來返回當前的會話資訊，常用的有如下資訊：

SELECT USERENV('CLIENT_INFO') CLIENT_INFO,         USERENV('LANGUAGE') 資料庫字符集,         USERENV('ISDBA') 是否DBA角色,         USERENV('SESSIONID') 當前會話標識符,         USERENV('ENTRYID') 可審計的會話標識符,         USERENV('LANG') 會話語言名稱的ISO簡記,         USERENV('INSTANCE') 當前的實例,         USERENV('TERMINAL') 當前電腦名    FROM DUAL;  

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。