|Oracle9i Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)
Part Number A89852-02
DBMS_SESSION , 11 of 19
This procedure deinstantiates all packages in this session: It frees all package state.
Memory used for caching execution state is associated with all PL/SQL functions, procedures, and packages that have been run in a session.
For packages, this collection of memory holds the current values of package variables and controls the cache of cursors opened by the respective PL/SQL programs. A call to
RESET_PACKAGE frees the memory associated with each of the previously run PL/SQL programs from the session, and, consequently, clears the current values of any package globals and closes any cached cursors.
RESET_PACKAGE can also be used to reliably restart a failed program in a session. If a program containing package variables fails, then it is hard to determine which variables need to be reinitialized.
RESET_PACKAGE guarantees that all package variables are reset to their initial values.
Because the amount of memory consumed by all executed PL/SQL can become large, you might use
RESET_PACKAGE to trim down the session memory footprint at certain points in your database application. However, make sure that resetting package variable values will not affect the application. Also, remember that later execution of programs that have lost their cached memory and cursors will perform slower, because they need to recreate the freed memory and cursors.
RESET_PACKAGE does not free the memory, cursors, and package variables immediately when called.
For example, PL/SQL procedure
P1 calls PL/SQL procedure
RESET_PACKAGE effects do not occur until procedure
P1 finishes execution (the PL/SQL call ends).
This SQL*Plus script runs a large program with many PL/SQL program units that may or may not use global variables, but it doesn't need them beyond this execution:
To free up PL/SQL cached session memory:
To run another large program: