SQL Script Pin All Packages

Updated: 30-Jun-1999
Version: 1.0

Description

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

Parameters

obj_size - Minumum size of objects to pin in bytes.

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links