-- Preload code table and PL/SQL package at instance startup.
-- NOTE: This is NOT a script, commands must be executed individually as appropriate!
-- Copyright (c) 2004, Caleb.com

-- Configure the instance for a keep buffer cache
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 1M;
SELECT * FROM V$BUFFER_POOL;

-- Create a table to utilize the keep buffer cache
CREATE TABLE dep_codes STORAGE ( BUFFER_POOL KEEP ) AS
   SELECT * FROM scott.DEPT;

-- Query to see what's in the buffer cache
COLUMN OBJECT_NAME FORMAT A40
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999

SELECT o.OBJECT_NAME, s.BUFFER_POOL, COUNT(*) NUMBER_OF_BLOCKS
     FROM DBA_OBJECTS o, DBA_SEGMENTS s, V$BH bh
    WHERE o.OBJECT_NAME = s.SEGMENT_NAME
      AND o.OWNER       = s.OWNER
      AND o.DATA_OBJECT_ID = bh.OBJD
      AND o.OWNER         = 'CALEB'
    GROUP BY o.OBJECT_NAME, s.BUFFER_POOL
    ORDER BY COUNT(*);
save bp replace

-- flushing the buffer cache will NOT flush the KEEP cache
ALTER SYSTEM FLUSH BUFFER_CACHE;
@bp

-- must create the DBMS_SHARED_POOL package (as SYS)
@$ORACLE_HOME/rdbms/admin/dbmspool.sql
GRANT EXECUTE ON dbms_shared_pool TO caleb;



-- Trigger to load code table and pin package on startup
CREATE OR REPLACE TRIGGER pin_startup
AFTER STARTUP ON DATABASE
DECLARE
   CURSOR dep_codes IS
      SELECT * FROM caleb.dep_codes;
BEGIN
   -- Load the tables we want to KEEP in memory
   FOR dep_rec IN dep_codes LOOP
      NULL;
   END LOOP;

   -- Load the PL/SQL packages (and triggers, etc) we want to KEEP in memory
   DBMS_SHARED_POOL.KEEP('sys.utl_file');

   -- If we get here then no exceptions occurred   
   INSERT INTO cs_log
      VALUES (sysdate, ora_login_user, 'Startup pin complete');
END;
/