Skip to Main Content
  • Questions
  • RESET_STATE vs DBMS_SESSION.MODIFY_PACKAGE_STATE (DBMS_SESSION.REINITIALIZE)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jon.

Asked: July 03, 2024 - 12:28 pm UTC

Last updated: September 11, 2025 - 4:43 am UTC

Version: 23

Viewed 1000+ times

You Asked

Thank you for taking my question.

We're getting ready for 23ai and see that there is a new RESET_STATE option we can use if we're using PL/SQL behind a web server connected to Oracle using a connection pool and serving up stateless services. ( https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/high-availability.html#GUID-F7E968E4-EE8F-4563-91F3-CD44B5D2E747 ) Up until now on 12.1.0.2 we've been running the following each time we return the connection to the pool to make sure we're not leaking data between calls:

 begin dbms_session.modify_package_state ( dbms_session.reinitialize ); end; 


It's always our top CPU consumer on the database. With that in mind...


1. Is RESET_STATE less demanding CPU wise than dbms_session.modify_package_state ( dbms_session.reinitialize )?

2. Was dbms_session.modify_package_state ( dbms_session.reinitialize ) the right way to go to prevent session state leaks on 12.1 or was there a better way?

and Connor said...

RESET_STATE is an Oracle exclusive feature that automatically stops session states leaking from one application usage to another.
Setting the session state in a request leaves the session with the current state, meaning that subsequent usages of that session can use the session state that has been set. For example, when an application borrows and returns a connection to a connection pool, if the sessions state is not cleared, the next usages of that connection can see the session state set in previous requests. Leaving session state in a session after returning that session to a pool, opens a vulnerability for elicit use of session state that was set.

Consider pooled applications where multiple users share the same connection. Application states such as Local Application Context can be used to set application information such as routing numbers, account numbers, addresses, medical and health care details.

RESET_STATE is a very important database feature that enables your developers to rely on the session state being clean when a session is returned to a connection pool with request boundaries. This can be an Oracle connection pool or a custom connection pool with added request boundaries. RESET_STATE clears session states guaranteed by the Oracle database.

RESET_STATE also improves your protection when using Transparent Application Continuity (TAC), because the session state is clean at the beginning of a new request.
RESET_STATE supersedes RESET_PACKAGE.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Initialization problem?

J. Sieben, July 04, 2025 - 9:07 am UTC

I encountered a similar problem and high CPU consumption was caused by the initialization code of the packages, not by resetting the package state. So it might be worthwhile looking into the initialization methods and try to avoid these methods wherever possible.
Chris Saxon
July 04, 2025 - 2:44 pm UTC

Thanks for your insights.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library