使用sys_context找出會話資訊
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
from dual
sys_context函式是Oracle提供的一個獲取環境上下文資訊的預定義函式。該函式用來返回一個指定namespace下的parameter值。該函式可以在SQL和PL/SQL語言中使用。
sys_context實際上就是一個Oracle儲存和傳遞引數的容器訪問函式。我們登入Oracle伺服器,是帶有會話資訊session_info和其他一些屬性資訊。其中,有一些是Oracle預定義的,登入系統的時候自動填入到指定的變數中。還有一些是我們自己定義到其中,用於傳遞值使用的。
下面是sys_context函式的使用格式:
sys_context(‘namespace’,’parameter’{,length});
其中,namespace是儲存資訊的一個組group單位,namespace是按照類別進行分類的。一個namespace下可以有多個引數值,通過不同的parameter進行區分。namespace是預先定義好的SQL識別符號,而parameter是可以任意大小寫非敏感的字串,不超過30位長度。
函式返回值為varchar2型別,長度預設為256位。如果需要限制這個預設值,可以資料length引數作為新的返回長度值。
設定namespace指定parameter值,可以使用dbms_session.set_context方法進行。
//自定義一個namespace,並且規定的設定的方法控制程式碼;…………………….step 1
SQL> create context Test using set_test_context;
Context created
//定義方法…………………….step 2
create or replace procedure set_test_context
(
vc_value in varchar2
)
is
begin
dbms_session.set_context('Test','a1',vc_value);
end set_test_context;
//設定上值…………………….step 3
SQL> exec set_test_context('m');
PL/SQL procedure successfully completed
//獲取這個值
SQL> select sys_context('Test','a1') from dual;
SYS_CONTEXT('TEST','A1')
------------------------------------
m
step1-3很重要,因為Test namespace為自定義的namespace,所以需要這樣的設定,以確定許可權所屬。
sys_context函式最常用的就是userenv名稱空間下的系列引數。下面是引數列表,摘自
http://hi.baidu.com/edeed/blog/item/28cba0ecaa6c8e3e269791bb.html;
Attribute |
Return Value |
ACTION |
Identifies the position in the module (application name) and is set through the DBMS_APPLICATION_INFO package or OCI. |
SELECTsys_context('USERENV', 'ACTION') FROM
dual; |
|
AUDITED_CURSORID |
Returns the cursor ID of the SQL that triggered the audit. This parameter is not valid in a fine-grained auditing environment. If you specify it in such an environment, Oracle Database always returns NULL. |
AUTHENTICATED_IDENTITY |
Returns the identity used in authentication. In the list that follows, the type of user is followed by the value returned:
|
AUTHENTICATION_DATA |
Data being used to authenticate the
login user. For X.503 certificate authenticated sessions, this field returns the
context of the certificate in HEX2 format. |
AUTHENTICATION_METHOD |
Returns the method of authentication. In the list that follows, the type of user is followed by the method returned.
|
BG_JOB_ID |
Job ID of the current session if it was established by an Oracle background process. Null if the session was not established by a background process. |
CLIENT_IDENTIFIER |
Returns an identifier that is set by the application through the DBMS_SESSION.SET_IDENTIFIER procedure, the OCI attribute OCI_ATTR_CLIENT_IDENTIFIER, or the Java class Oracle.jdbc.OracleConnection.setClientIdentifier. This attribute is used by various database components to identify lightweight application users who authenticate as the same user. |
SELECTsys_context('USERENV', 'CLIENT_IDENTIFIER') FROM
dual; |
|
CLIENT_INFO |
Returns user session information that can be stored by an application using the DBMS_APPLICATION_INFO package. |
SELECTsys_context('USERENV', 'CLIENT_INFO') FROM
dual; |
|
CURRENT_BIND |
The bind variables for fine-grained auditing |
CURRENT_EDITION_ID |
The name of the current edition |
SELECTsys_context('USERENV', 'CURRENT_EDITION_ID') FROM dual; |
|
CURRENT_EDITION_NAME |
The name of the current edition |
SELECTsys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual; |
|
CURRENT_SCHEMA |
Name of the default schema being used in the current schema. This value can be changed during the session with an ALTER SESSION SET CURRENT_SCHEMA statement. |
SELECTsys_context('USERENV', 'CURRENT_SCHEMA') FROM dual; |
|
CURRENT_SCHEMAID |
Identifier of the default schema being used in the current session. |
SELECTsys_context('USERENV', 'CURRENT_SCHEMAID') FROM
dual; |
|
CURRENT_SQL |
Returns the first 4K bytes of the current SQL that triggered the fine-grained auditing event. |
CURRENT_SQLn |
CURRENT_SQLnattributes return subsequent 4K-byte increments, where n can be an integer from 1 to 7, inclusive. CURRENT_SQL1 returns bytes 4K to 8K; CURRENT_SQL2 returns bytes 8K to 12K, and so forth. You can specify these attributes only inside the event handler for the fine-grained auditing feature. |
CURRENT_SQL_LENGTH |
The length of the current SQL statement that triggers fine-grained audit or row-level security (RLS) policy functions or event handlers. Valid only inside the function or event handler. |
DB_DOMAIN |
Domain of the database as specified in the DB_DOMAIN initialization parameter. |
SELECTsys_context('USERENV', 'DB_DOMAIN') FROM dual; |
|
DB_NAME |
Name of the database as specified in the DB_NAME initialization parameter. |
SELECTsys_context('USERENV', 'DB_NAME') FROM
dual; |
|
DB_UNIQUE NAME |
Name of the database as specified in the DB_UNIQUE_NAME initialization parameter. |
SELECTsys_context('USERENV', 'DB_UNIQUE_NAME') FROM
dual; |
|
ENTRYID |
The available auditing entry identifier. You cannot use this option in distributed SQL statements. To use this keyword in USERENV, the initialization parameter AUDIT_TRAIL must be set to true. |
ENTERPRISE_IDENTITY |
Returns the user's enterprise-wide identity:
The value of the attribute differs by proxy method:
|
FG_JOB_ID |
Job ID of the current session if it was established by a client foreground process. Null if the session was not established by a foreground process. |
GLOBAL_CONTEXT_MEMORY |
The number used in the System Global Area by the globally accessed context. |
SELECTsys_context('USERENV', 'GLOBAL_CONTEXT_MEMORY') FROM dual; |
|
GLOBAL_UID |
Returns the global user ID from Oracle Internet Directory for Enterprise User Security (EUS) logins; returns null for all other logins. |
HOST |
Name of the host machine from which the client has connected. |
SELECTsys_context('USERENV', 'HOST') FROM dual; |
|
IDENTIFICATION_TYPE |
Returns the way the user's schema was created in the database. Specifically, it reflects the IDENTIFIED clause in the CREATE/ALTER USER syntax. In the list that follows, the syntax used during schema creation is followed by the identification type returned:
|
SELECTsys_context('USERENV', 'IDENTIFICATION_TYPE') FROM dual; |
|
INSTANCE |
The instance identification number of the current instance. |
SELECTsys_context('USERENV', 'INSTANCE') FROM dual; |
|
INSTANCE_NAME |
The name of the instance. |
SELECTsys_context('USERENV', 'INSTANCE_NAME') FROM dual; |
|
IP_ADDRESS |
IP address of the machine from which the client is connected. |
ISDBA |
TRUE if the session is SYS |
SELECTsys_context('USERENV', 'ISDBA') FROM dual; |
|
LANG |
The ISO abbreviation for the language name, a shorter form. than the existing 'LANGUAGE' parameter. |
SELECTsys_context('USERENV', 'LANG') FROM dual; |
|
LANGUAGE |
The language and territory currently used by your session, along with the
database character set, in the
form.: |
SELECTsys_context('USERENV', 'LANGUAGE') FROM dual; |
|
MODULE |
The application name (module) set through the DBMS_APPLICATION_INFO package or OCI. |
SELECTsys_context('USERENV', 'MODULE') FROM dual; |
|
NETWORK_PROTOCOL |
Network protocol being used for communication, as specified in the 'PROTOCOL=protocol' portion of the connect string. |
NLS_CALENDAR |
The current calendar of the current session. |
SELECTsys_context('USERENV', 'NLS_CALENDAR') FROM dual; |
|
NLS_CURRENCY |
The currency of the current session. |
SELECTsys_context('USERENV', 'NLS_CURRENCY') FROM dual; |
|
NLS_DATE_FORMAT |
The date format for the session. |
SELECTsys_context('USERENV', 'NLS_DATE_FORMAT') FROM dual; |
|
NLS_DATE_LANGUAGE |
The language used for expressing dates. |
SELECTsys_context('USERENV', 'NLS_DATE_LANGUAGE') FROM dual; |
|
NLS_SORT |
BINARY or the linguistic sort basis. |
SELECTsys_context('USERENV', 'NLS_SORT') FROM dual; |
|
NLS_TERRITORY |
The territory of the current session. |
SELECTsys_context('USERENV', 'NLS_TERRITORY') FROM dual; |
|
OS_USER |
Operating system username of the client process that initiated the database session. |
SELECTsys_context('USERENV', 'OS_USER') FROM dual; |
|
POLICY_INVOKER |
The invoker of row-level security (RLS) policy functions. |
PROXY_ENTERPRISE_IDENTITY |
Returns the Oracle Internet Directory DN when the proxy user is an enterprise user. |
PROXY_GLOBAL_UID |
Returns the global user ID from Oracle Internet Directory for Enterprise User Security (EUS) proxy users; returns NULL for all other proxy users. |
PROXY_USER |
Name of the database user who opened the current session on behalf of SESSION_USER. |
PROXY_USERID |
Identifier of the database user who opened the current session on behalf of SESSION_USER. |
SERVER_HOST |
The host name of the machine on which the instance is running. |
SELECTsys_context('USERENV', 'SERVER_HOST') FROM dual; |
|
SERVICE_NAME |
The name of the service to which a given session is connected. |
SELECTsys_context('USERENV', 'SERVICE_NAME') FROM dual; |
|
SESSION_USER |
Database user name by which the current user is authenticated. This value remains the same throughout the duration of the session. |
SELECTsys_context('USERENV', 'SESSION_USER') FROM dual; |
|
SESSION_USERID |
Identifier of the database user name by which the current user is authenticated. |
SELECTsys_context('USERENV', 'SESSION_USERID') FROM dual; |
|
SESSIONID |
The auditing session identifier. You cannot use this option in distributed SQL statements. This is the equivalent to the AUDSID column ingv$session. |
SELECTsys_context('USERENV', 'SESSIONID') FROM dual; |
|
SID |
The session number (different from the session ID). |
SELECTsys_context('USERENV', 'SID') FROM dual; |
|
STATEMENTID |
The auditing statement identifier. STATEMENTID represents the number of SQL statements audited in a given session. |
TERMINAL |
The operating system identifier for the client of the current session. In distributed SQL statements, this option returns the identifier for your local session. In a distributed environment, this is supported only for remote SELECT statements, not for remote INSERT, UPDATE, or DELETE operations. (The return length of this parameter may vary by operating system.) |
SELECTsys_context('USERENV', 'TERMINAL') FROM dual; |
例子:
SQL> select sys_context('userenv','ip_address') from dual;
SYS_CONTEXT('USERENV','IP_ADDR
-----------------------------------
169.254.94.86
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27500440/viewspace-1839620/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- sys_context獲取當前會話的屬性Context會話
- oracle如何找出登入會話v$session的IP地址?Oracle會話Session
- 茴字的三種寫法-找出當前會話ID會話
- 使用SYS_CONTEXTContext
- 會話統計資訊session_pkg會話Session
- 獲取當前會話資訊的方法會話
- 從V$SESSMETRIC檢視中找出當前最佔用資源的會話SSM會話
- 獲取當前會話資訊的方法(二)會話
- 資料庫會話記錄使用者登陸的密碼資訊資料庫會話密碼
- 使用Spring Security控制會話Spring會話
- 在檢視中使用會話會話
- 使用screen管理遠端會話會話
- 如何找出哪個Mac應用在使用位置資訊並關掉它Mac
- 使用context_info傳遞上下文資訊禁用會話級觸發器Context會話觸發器
- 如何使用 byobu 複用 SSH 會話會話
- 使用Redis實現分散式會話Redis分散式會話
- 使用 Tinx 過載 Laravel Tinker 會話Laravel會話
- 檢視歷史會話等待事件對應的session資訊會話事件Session
- 怎樣獲取發生錯誤的會話資訊 -- 轉會話
- 怎樣獲取發生錯誤的會話資訊(轉)會話
- 怎樣獲取發生錯誤的會話資訊 zt會話
- SYS_CONTEXTContext
- 使用screen 管理你的遠端會話會話
- 白話資訊熵熵
- Windows下大量SYSMAN會話超出會話限制Windows會話
- 會話管理會話
- Session會話Session會話
- 【SYS_CONTEXT】使用SYS_CONTEXT獲取連線到資料庫伺服器的IP地址Context資料庫伺服器
- Windows檢視登入使用者會話Windows會話
- 使用Oracle PROFILE控制會話空閒時間Oracle會話
- oracle 10046其他使用者會話Oracle會話
- oracle 跟蹤當前使用者會話Oracle會話
- 使用sql trace工具和tkprof來跟會話SQL會話
- SYS_CONTEXT & USERENVContext
- 資料處理,會“說話”的大機器——資料資訊圖
- MQTT-會話MQQT會話
- Oracle 會話(Session)Oracle會話Session
- oracle鎖會話Oracle會話