SQL Script Pin All Standard Packages

Updated: 20-Mar-2004
Version: 2.0

Description

Pin all packages standard database packages. This script should be run on database startup so to improve performance

Parameters

None

SQL Source

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

spool pinpkgs.lst

set serveroutput on size 1000000

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 

PROMPT 
PROMPT Pinning Standard SYS Packages

CREATE OR REPLACE PROCEDURE pin_pkgs AS
BEGIN
   dbms_shared_pool.keep  ('SYS.STANDARD');
   dbms_shared_pool.keep  ('SYS.DBMS_STANDARD');
   dbms_shared_pool.keep  ('SYS.DBMS_DESCRIBE');
   dbms_shared_pool.keep  ('SYS.DBMS_OUTPUT');

--
--If you use the PL SQL Toolkit, Pin These....
--

--   dbms_shared_pool.keep('SYS.DIUTIL');
--   dbms_shared_pool.keep('SYS.WPIUTL');
--   dbms_shared_pool.keep('SYS.HTP');
--   dbms_shared_pool.keep('SYS.HTF');
--   dbms_shared_pool.keep('SYS.OWA_UTIL');
--   dbms_shared_pool.keep('SYS.WPG_DOCLOAD');

--
-- If you use Designer 2000 Release 1

--   dbms_shared_pool.keep('SYS.DBMS_LOCK');
--   dbms_shared_pool.keep('SYS.DBMS_UTILITY');
--   dbms_shared_pool.keep('SYS.DBMS_TRANSACTION');
--   dbms_shared_pool.keep('.CIIUTL');
--   dbms_shared_pool.keep('.RMMAC');
--   dbms_shared_pool.keep('.RMMAN);
--   dbms_shared_pool.keep('.RMMDQ');
--   dbms_shared_pool.keep('.RMMES);
--   dbms_shared_pool.keep('.RMOACTIVITY');
--   dbms_shared_pool.keep('.RMMVI');

--
-- If you use Designer 2000 Release 2

--   dbms_shared_pool.keep('SYS.UTL_FILE');
--   dbms_shared_pool.keep('.CDAPI');
--   dbms_shared_pool.keep('.CIILOK');
--   dbms_shared_pool.keep('.CIIACC');
--   dbms_shared_pool.keep('.CIIUTL');
--   dbms_shared_pool.keep('.CIITYP');
--   dbms_shared_pool.keep('.CIOAPP_CON_INTERFACE');
--   dbms_shared_pool.keep('.RMOASSERTION');
--   dbms_shared_pool.keep('.RMOACTIVITY');
--   dbms_shared_pool.keep('.RMMVI');
--   dbms_shared_pool.keep('.RMMES');
--   dbms_shared_pool.keep('.RMMDQ');
--   dbms_shared_pool.keep('.RMMDC');
--   dbms_shared_pool.keep('.RMMAN');
--   dbms_shared_pool.keep('.RMMAC');
--   dbms_shared_pool.keep('.RMDBG');
--   dbms_shared_pool.keep('.RMAND');
--   dbms_shared_pool.keep('.RM');
--   dbms_shared_pool.keep('.CIEVAL');
--   dbms_shared_pool.keep('.CK_APP_LOG_TR1');
--   dbms_shared_pool.keep('.CITARUSDD_ELEMENTS');
--   dbms_shared_pool.keep('.CITASDSDD_ELEMENTS');
--   dbms_shared_pool.keep('.CITASISDD_ELEMENTS');
--   dbms_shared_pool.keep('.CDI_EL_UPDATE_AUDIT');
--   dbms_shared_pool.keep('.CITARDSDD_STRUCTURE_ELEMENTS');
--   dbms_shared_pool.keep('.CITARUSDD_STRUCTURE_ELEMENTS');
--   dbms_shared_pool.keep('.CITASISDD_STRUCTURE_ELEMENTS');
--   dbms_shared_pool.keep('.CDI_STR_CREATE_AUDIT');
--   dbms_shared_pool.keep('.CDI_STR_UPDATE_AUDIT');
--   dbms_shared_pool.keep('.CITASUSDD_ELEMENTS');
--   dbms_shared_pool.keep('.CITARISDD_STRUCTURE_ELEMENTS');
--   dbms_shared_pool.keep('.CITARDSDD_ELEMENTS');
--   dbms_shared_pool.keep('.CDI_EL_CREATE_AUDIT');
--   dbms_shared_pool.keep('.CITASDSDD_STRUCTURE_ELEMENTS');
--   dbms_shared_pool.keep('.CITARISDD_ELEMENTS');
--   dbms_shared_pool.keep('.CITASUSDD_STRUCTURE_ELEMENTS');
END;
/

execute pin_pkgs;

PROMPT 
PROMPT This could easily be put in a database startup trigger
PROMPT

CREATE OR REPLACE TRIGGER db_startup_pin AFTER STARTUP ON DATABASE   
BEGIN
     pin_pkgs;
END;
/

spool off

Related Links

Oracle 10g: (SQL Script) Pin All Packages

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