Re: pinning procedures

spool pinpkgs.lst

set serveroutput on

PROMPT PROMPT Ensure you have run the following as SYS

PROMPT $ORACLE_HOME/rdbms/admin/dbmspool.sql

PROMPT $ORACLE_HOME/rdbms/admin/prvtpool.plb

PROMPT PROMPT PROMPT Listing all objects in shared_pool over 75k

PROMPT execute dbms_shared_pool.sizes(75);

ACCEPT obj_size prompt 'Enter size of objects to pin (in bytes) : '

DECLARE    CURSOR get_pkgs IS SELECT owner, name, sharable_mem

    FROM v$db_object_cache

    WHERE sharable_mem > &obj_size

    AND type IN

    ('PACKAGE', 'FUNCTION', 'PROCEDURE')     AND kept = 'NO';

BEGIN    FOR pkgs_rec IN get_pkgs LOOP

       dbms_shared_pool.keep(pkgs_rec.owner

                      || '.'

                      || pkgs_rec.name, 'P');

       dbms_output.put_line ('Pinned Package '

                            ||pkgs_rec.owner

                            || '.'

                            || pkgs_rec.name

        || ' of size '

        || pkgs_rec.sharable_mem ||' bytes.');

   END LOOP; END; /

spool off

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

Steve Cormier <scormier_at_idirect.com> wrote in message news:ev_P3.6033$WM2.92576_at_quark.idirect.com...
> I want to pin some procedures and triggers in the SGA
> upon starting my database. I know to use the DBMS_SHARED_POOL.KEEP
> package but it mentions in the Oracle Doc to reference each object into
> the SGA before you issue the KEEP procedure. What command can I use
> to reference it? Will alter procedure <object_name> compile work?
> All answers are greatly appreciated
>
>
>
Received on Fri Oct 22 1999 - 13:41:13 CDT