Reset Database Session State

RESET_STATE is an Oracle-exclusive feature that automatically prevents session states from leaking between application requests.

Note:

Starting with Oracle AI Database 26ai, RESET_STATE does not have a dependency on the Application Continuity feature.

When an application returns a connection to a connection pool, the session can retain state set by the previous request. If the state is not cleared, a subsequent user or request can access the leftover state. For example, when an application borrows and returns a connection to a connection pool, if the sessions state is not cleared, the next user of that connection can see the session state left behind by the previous request. Leaving session state in a session after it is returned to a pool creates a vulnerability for unintended or malicious use of the leftover state.

Consider pooled applications where multiple users share the same connection. Application states such as Local Application Context may store sensitive information like routing numbers, account numbers, addresses, or medical and healthcare details.

RESET_STATE is a critical database feature that ensures developers can rely on a clean session state whenever a session is returned to a connection pool with request boundaries. This applies to both Oracle connection pools and custom connection pools with added request boundaries. RESET_STATE guarantees that the Oracle AI Database clears session states.

Additionally, RESET_STATE also improves protection when using Transparent Application Continuity (TAC) by ensuring that the next request begins with clean session state.

The following reset-state levels are available:

NONE: Does not perform session-state cleanup between requests. NONE is the default value.
LEVEL1: Clears session state that cannot be restored reliably between requests.
LOGIN: Performs the LEVEL1 cleanup and restores session state to the state captured at the end of login.

Note:

RESET_STATE supersedes RESET_PACKAGE.

RESET_STATE=LEVEL1

When RESET_STATE is set to LEVEL1, Oracle AI Database clears session state that cannot be restored reliably at the end of a request, which includes:

Open cursors that cannot be reused
PL/SQL package and global variables
Multilingual Engine (MLE) variables
Session-duration temporary tables
Session-duration temporary LOBs
Session-local sequences
The DBMS_OUTPUT buffer

Use LEVEL1 when the connection pool or client does not support restoration of login-time session state.

RESET_STATE=LOGIN

When RESET_STATE is set to LOGIN, it performs the LEVEL1 cleanup and restores session state to the state captured at the end of login. The login-time state can include:

Whether DBMS_OUTPUT was enabled or disabled at logon
Session-modifiable initialization parameters
Local application context values returned by SYS_CONTEXT
Non-password-protected roles
Dedicated database links
The SQL_TRACE setting
End-to-end tracing attributes, including module, action, and execution context identifier (ECID)
CLIENT_INFO
CLIENT_IDENTIFIER

Use LOGIN when the application requires the session to be restored to the state captured at the end of login. RESET_STATE=LOGIN is applied at the end of an explicit request boundary. The client or connection pool must define request boundaries for Oracle AI Database to apply the reset operation.