SQL Script Pin All Standard Packages

Updated : 20-Mar-2002
Version : 1.0

Description

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

Parameters

None.

SQL Source

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('<repository_owner>.CIIUTL');
--   dbms_shared_pool.keep('<repository_owner>.RMMAC');
--   dbms_shared_pool.keep('<repository_owner>.RMMAN);
--   dbms_shared_pool.keep('<repository_owner>.RMMDQ');
--   dbms_shared_pool.keep('<repository_owner>.RMMES);
--   dbms_shared_pool.keep('<repository_owner>.RMOACTIVITY');
--   dbms_shared_pool.keep('<repository_owner>.RMMVI');

--
-- If you use Designer 2000 Release 2

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

Return to Index of SQL Scripts

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