Reset Of Kept Packages

DCARS:desc revealnet_kept_objects;
 Name                            Null?    Type
 ------------------------------- -------- ----
 OBJECT_NAME                     NOT NULL VARCHAR2(128)

Figure 4: Example Table of Kept Objects

The procedure redo_pin is used to periodically un-pin objects, flush the shared pool and re-pin objects listed in the revealnet_kept_objects table. The procedure is shown in figure 5. By scheduling the procedure to run once per day at a low usage time, you can prevent shared pool fragmentation from causing problems for your larger packages and prevent aging of these packages out of the shared pool.

PROCEDURE redo_pin AS
       com           VARCHAR2(100);
       com_cur        INTEGER;
       processed        INTEGER;
       i             BINARY_INTEGER:=1;
       j             BINARY_INTEGER:=0;

       TYPE objects IS
              TABLE OF
              revealnet_kept_objects.object_name%TYPE
              INDEX BY BINARY_INTEGER;
       object               OBJECTS;
--
--
       CURSOR get_objects IS
              SELECT
                     object_name
              FROM

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm