Oracle Database Tips by Donald Burleson
Here is a discussion on how
serially_reusable packages work:
Why Serially Reusable Packages?
Because the state of a non-reusable package
persists for the lifetime of the session, this locks up UGA
memory for the whole session. In applications, such as
Oracle Office, a log-on session can typically exist for days
together. Applications often need to use certain packages
only for certain localized periods in the session and would
ideally like to de-instantiate the package state in the
middle of the session, after they are done using the
packages, application developers have a way of modelling
their applications to manage their memory better for
scalability. Package state that they care about only for the
duration of a call to the server should be captured in
Oracle documentation also has details on creating serially
reusable packages with the pragma serially_reusable:
A package that is marked
SERIALLY_REUSABLE has the following properties:
- Its package variables are
meant for use only within the work boundaries, which
correspond to calls to the server (either OCI call
boundaries or PL/SQL RPC calls to the server).
If the application
programmer makes a mistake and depends on a
package variable that is set in a previous
unit of work, then the application program
can fail. PL/SQL cannot check for such
- A pool of package
instantiations is kept, and whenever a "unit of work"
needs this package, one of the instantiations is
"reused", as follows:
- The package
variables are reinitialized (for example, if the
package variables have default values, then those
values are reinitialized).
- The initialization
code in the package body is run again.
- At the "end work"
boundary, cleanup is done.
- If any cursors were
left open, then they are silently closed.
- Some non-reusable
secondary memory is freed (such as memory for
collection variables or long
- This package
instantiation is returned back to the pool of
reusable instantiations kept for this package.
- Serially reusable
packages cannot be accessed from database triggers or
other PL/SQL subprograms that are called from SQL
statements. If you try, then Oracle Database generates
Here are other references to "pragma
serially_reusable" in the
Oracle PL/SQL Developers Guide: