List the KEPT status for PL/SQL objects in your database

This SQL*Plus script lets you list the KEPT status for PL/SQL objects (Packages, Procedures, Functions, and Triggers) in your database. You can show objects with a KEPT status of YES, NO, or both, and select the schema to show using standard Oracle wildcards. The script also lists the number of loads and executions for each object.

-- ShowKeptObjects.sql

set verify off

col owner form a15 wrap
col name  form a30 trunc
col type  form a12 trunc
col which new_value which noprint
col whose new_value whose noprint

accept which_in prompt 'Show kept status of - (A)ll, (Y)es, (N)o [Y]: '
accept whose_in prompt 'Show kept status for schema pattern [%]: '

SELECT DECODE( upper( substr( '&which_in', 1, 1)), 
             'Y', 'YES',
               'N', 'NO',
               'A', 'YES'', ''NO',
               'YES'
           ) which
FROM   DUAL;

SELECT DECODE( nvl( length(substr( '&whose_in', 1, 1)), 0), 
             0, '%',
               '&whose_in'
           ) whose
FROM   DUAL;

set pages 40 lines 100 pause on
set pause 'Press a key for more results...'

select owner, type, name, kept, loads, executions execs
from  v$db_object_cache
where type in ('PACKAGE', 
               'PACKAGE BODY', 
               'PROCEDURE', 
               'FUNCTION', 
               'TRIGGER')
and   kept IN ( '&which')
AND   owner LIKE UPPER( '&whose')
order by owner, type, name;

set pause off verify on

Reader Feedback

Zuwei X. writes: The output of this script is nice; however, I noticed the exec column displays a value 0 for all the objects, either kept or not--some objects even have thousands of reloads. Part of the output is below...

OWNER           TYPE         NAME                 KEPT LOADS      EXECS
--------------- ------------ -------------------- ---- ---------- ----------
BENEFITS        TRIGGER      BAD_AIU_TRG          NO   1          0
BENEFITS        TRIGGER      BAD_TRG              NO   1          0
BENEFITS        TRIGGER      BD_BU_TRG            NO   5          0
BENEFITS        TRIGGER      CB_TRIGGER           NO   4          0
BENEFITS        TRIGGER      HC_TRIGGER           NO   644        0
DMG             PACKAGE      DMG_PACKAGE          NO   1587       0
HRIS            PACKAGE      HRIS_PACKAGE         NO   4642       0

etc...

Michel B. writes: The script works well for me but the number of exec is always null due to bug 1764313, fixed in 9.0.2. At this time there is no workaround for getting the executions information. So your best bet is to pin common standard procedures and rely on your knowledge of the application to decide what to pin in the shared pool.

For More Information

• What do you think about this tip? E-mail the editor at tdichiara@techtarget.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.