Oracle Application Context详解(原创)

概述

Application Context是内存中的一组name-value对，application context从属于某个命名空间(namespace)。
用户只能通过一个自定义procedure调用dbms_session.set_context来设置application context的值。用户使用sys_context(<namespace>,<name>)来获取某个 application context的值。
Application context分为三种

dabase session-based application context，又称为local application context。Local application context存储在UGA中，dedicated server mode,UGA在PGA中，shared server mode，UGA在SGA中。local application context是会话/server process级别的。只有本会话能够访问。当会话/server process终止时，local application context的生命周期也结束了。另一篇博客Oracle VPD http://blog.csdn.net/chncaesar/article/details/18550029 给出了一个local application context的例子。
- global application context，Global application context存储在SGA中，只要SGA不消亡，global application context就一直存在。global application context常常用于跨会话，与会话无关的场景。dbms_session.set_context有两个默认值为NULL的参数：
  username
  client_id
  username = null,client_id=null。所有用户都能访问。
  username = null,client_id=<client_id>。只要session 的client_id =<client_id>，而不管username，都能访问。
  username = <database username>,client_id=null。只要session使用指定的oracle schema登陆，不管client_id，都能访问。
  username = <database username>,client_id=<client_id>. 常用于statless web session ，如http。
  username = <non-database username>,client_id=<client_id> . 该username将用于数据库连接池的owner。
client session-based application context。通常被OCI使用，存储在客户端内存，而不是Oracle服务器端，也由OCI程序管理。

实例

例子1：所有用户都能访问的application context

CREATE OR REPLACE CONTEXT global_hr_ctx USING hr_ctx_pkg ACCESSED GLOBALLY;
CREATE OR REPLACE PACKAGE hr_ctx_pkg
AS
PROCEDURE set_hr_ctx(sec_level IN VARCHAR2);
PROCEDUREclear_hr_ctx;
END;
/
create or replace package body hr_ctx_pkg
as
procedure set_hr_ctx(sec_level in VARCHAR2)
BEGIN
DBMS_SESSION.SET_CONTEXT(
NAMESPACE => 'global_hr_ctx',
attribute => 'job_role',0)"> value => sec_level);
end set_hr_ctx;
procedure clear_hr_ctx
as
begin
dbms_session.clear_context('global_hr_ctx','job_role');
end clear_hr_ctx;
end;
/

sql> BEGIN
2 hr_ctx_pkg.set_hr_ctx('clerk');
3 END;
4 /

PL/sql procedure successfully completed.

sql> SELECT SYS_CONTEXT('global_hr_ctx','job_role') job_role FROM DUAL;
JOB_ROLE
------------------------------------
clerk

例子2: 跨session/application，但是使用同一个数据库schema的application context。

Setting a Global Context for Database Users Who Move Between Applications
CREATE OR REPLACE PACKAGE hr_ctx1_pkg
AS
PROCEDURE set_hr_ctx(sec_level IN VARCHAR2,user_name IN VARCHAR2);
PROCEDURE clear_hr_context;
END;
/
CREATE OR REPLACE PACKAGE BODY hr_ctx1_pkg
AS
PROCEDURE set_hr_ctx(sec_level IN VARCHAR2,user_name IN VARCHAR2)
AS
BEGIN
DBMS_SESSION.SET_CONTEXT(
namespace => 'global_hr_ctx',
attribute => 'job',
value => sec_level,
username => user_name); --- 这里的user_name表示用户为数据库用户
END set_hr_ctx;
PROCEDURE clear_hr_context
AS
BEGIN
DBMS_SESSION.CLEAR_CONTEXT('global_hr_ctx');
END clear_hr_context;
END hr_ctx1_pkg;
/
例3 Tutorial: Creating a Global Application Context That Uses a Client Session ID

Step 1: Create User Accounts

You must create two users for this example: a security administrator who will manage the application context and its package,and a user account that owns the connection pool.

In this tutorial:

Log on to sql*Plus asSYSand connect usingAS SYSDBA.
```
sqlplus sys as sysdba
Enter password: password
```
Create thesysadmin_ctxaccount,who will administer the global application context.
```
GRANT CREATE SESSION,CREATE ANY CONTEXT,CREATE PROCEDURE TO sysadmin_ctx IDENTIFIED BY password;

GRANT EXECUTE ON DBMS_SESSION TO sysadmin_ctx;
```
Replacepasswordwith a password that is secure. See"Minimum Requirements for Passwords"for more information.
Create the database accountapps_user,who will own the connection pool.
```
GRANT CREATE SESSION TO apps_user IDENTIFIED BY password;
```
Replace"Minimum Requirements for Passwords"for more information.

 
 
 
  Step 2: Create the Global Application Context 
  
    Log on as the security administratorsysadmin_ctx.
 CONNECT sysadmin_ctx
Enter password: password
  
    Create thecust_ctxglobal application context.
 CREATE CONTEXT global_cust_ctx USING cust_ctx_pkg ACCESSED GLOBALLY;
  Thecust_ctxcontext is created and associated with the schema of the security administratorsysadmin_ctx. However,theSYSschema owns the application context.

CREATE OR REPLACE PACKAGE cust_ctx_pkg AS PROCEDURE set_session_id(session_id_p IN NUMBER); PROCEDURE set_cust_ctx(sec_level_attr IN VARCHAR2,sec_level_val IN VARCHAR2); PROCEDURE clear_hr_session(session_id_p IN NUMBER); PROCEDURE clear_hr_context; END; / CREATE OR REPLACE PACKAGE BODY cust_ctx_pkg AS session_id_global NUMBER; PROCEDURE set_session_id(session_id_p IN NUMBER) AS BEGIN session_id_global := session_id_p; DBMS_SESSION.SET_IDENTIFIER(session_id_p); END set_session_id; PROCEDURE set_cust_ctx(sec_level_attr IN VARCHAR2,sec_level_val IN VARCHAR2) AS BEGIN DBMS_SESSION.SET_CONTEXT( namespace => 'global_cust_ctx',attribute => sec_level_attr,value => sec_level_val,username => USER,-- Retrieves the session user,in this case,apps_user client_id => session_id_global); END set_cust_ctx; PROCEDURE clear_hr_session(session_id_p IN NUMBER) AS BEGIN DBMS_SESSION.SET_IDENTIFIER(session_id_p); DBMS_SESSION.CLEAR_IDENTIFIER; END clear_hr_session; PROCEDURE clear_hr_context AS BEGIN DBMS_SESSION.CLEAR_CONTEXT('global_cust_ctx',session_id_global); END clear_hr_context; END; /

CREATE OR REPLACE PACKAGE set_empno_ctx_pkg IS PROCEDURE set_empno; END; / CREATE OR REPLACE PACKAGE BODY set_empno_ctx_pkg IS PROCEDURE set_empno IS emp_id HR.EMPLOYEES.EMPLOYEE_ID%TYPE; BEGIN SELECT EMPLOYEE_ID INTO emp_id FROM HR.EMPLOYEES WHERE email = SYS_CONTEXT('USERENV','SESSION_USER'); DBMS_SESSION.SET_CONTEXT('empno_ctx','employee_id',emp_id); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END; /

Oracle Application Context详解(原创)

Step 2: Create the Global Application Context

Step 3: Create a Package for the Global Application Context

Step 4: Test the Global Application Context

Tutorial: Creating and Using a Database Session-Based Application Context

Step 1: Create User Accounts and Ensure the User SCOTT Is Active

Step 2: Create the Database Session-Based Application Context

Step 3: Create a Package to Retrieve Session Data and Set the Application Context

Step 4: Create a Logon Trigger for the Package

Step 5: Test the Application Context

Step 6: Remove the Components for This Tutorial

猜你在找的Oracle相关文章