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
- Steven Feuerstein Master Class. Anti-Pattern PL/SQL Programming + 12c New PL/SQL Features, December 12 and 13 2013
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- On the integrity of data in Java applications – presentation from JFall 2013
- Enriching XMLType data using relational data – XQuery and fn:collection in action
- Java 8 – Collection enhancements leveraging Lambda Expressions – or: How Java emulates SQL
- Oracle Database SQL – Recursive Subquery to inspect events in football matches – find the MVP
- Oracle Database 12c: Find most valuable player using MATCH_RECOGNIZE in SQL
- Oracle Database 12c: Pattern Matching through MATCH_RECOGNIZE in SQL
- Oracle Database 12c: joining and outer joining with collections
- Oracle Database 12c: PL/SQL package UTL_CALL_STACK for programmatically inspecting the PL/SQL Call Stack