SQL Script Pin All Packages

Updated : 30-June-1999
Version : 1.1

Description

Pin all packages that exist in the database above a minimum size. This script should be run on database startup so that all SYS packages are pinned.

Parameters

obj_size - Minumum size of objects to pin in bytes.

SQL Source

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

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal