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:

Resources

The SQL script that goes with these examples: globallyaccessiblecontext.txt.