Oracle Database Cross Session Data Cache – Introducing the Globally Accessible Database Context
An Application Context in the Oracle Database is a name in-memory collection of key-value pairs. Applications can store values in an Application Context – using a package associated with the context – and retrieve values from them using sys_context( name of context, key for value). Sys_context can be used in PL/SQL as well as in SQL. Note that references to context values using sys_context are treated as bind-variables by the SQL engine – and evaluated only once at the start of the query.
An application context is usually associated with a session, its data stored in the UGA and the values only accessible within the session itself. There is however a second type of application context, that is accessible from all sessions in the database instance. The data in such a global application context lives in the SGA and survives the end of the session that stored the data in the context – as well as other session until the database shuts down.
This type of context can be used to exchange and share data between sessions. In particular it can be used as a global cache for global settings and values that are used frequently but may be somewhat expensive to retrieve. This article shows how to create a globally accessible application context and how to use it.
First create the PL/SQL package that will control the context of the application context, for example:
create or replace package cache_mgr as procedure put_in_cache ( p_key in varchar2 , p_value in varchar2 ); end;
Then create the Application Context itself; specify ACCESSED GLOBALLY to make it a global application context:
Now is a good time to flesh out the package body as well:
create or replace package body cache_mgr as procedure put_in_cache ( p_key in varchar2 , p_value in varchar2 ) is begin DBMS_SESSION.SET_CONTEXT( 'global_cache',p_key ,p_value); end put_in_cache; end;
Store a value in the global cache, effectively publishing it to all sessions in the database:
You can retrieve the value in the normal way using sys_context in the same session:
And verify that the value is indeed part of the Global Context Data Space:
If we connect anew as the samne or as another user – starting a new session – we can access the joke of the day from the global_cache application context:
The SQL script that goes with these examples: globallyaccessiblecontext.txt.
- Little trick to set the database context when using the ADF BC Tester
- Select Trigger in Oracle Database – introducing Fine Grained Auditing
- Virtual Private Database, securing your data
- Oracle BPEL Process Manager: Most efficient way to manage dynamic process level (cross instance) data
- New in Oracle 11g: PL/SQL Function Result Cache
- Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR
- Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring
- Oracle SQL: Using subquery factoring in an INSERT statement
- OTN Yathra 2013 – Spreading the story of Oracle across India – (Half time)
- ADF DVT Speed Date: Present Metrics per Year, Quarter and Month using a zoom-enabled ADF DVT Resource Utilization Gantt and ADF BC
- Using TRUNC in SQL to get the first date in a period
- Read an Excel xlsx with PL/SQL
- ADF DVT: Visualizing valid periods using Project and Scheduling Gantt Charts
- Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions
- The Very Very Latest in Database Development – slides from the Expertezed presentation