Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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