设置数据库启动时自动keep对象

step1、创建表，保存需要keep的对象
   create table sys.pin_objects (owner varchar2(30), name varchar2(30));

step2、创建包，将需要keep的对象插入pin_objects表
create or replace procedure sys.sp_list_objs as
      pragma AUTONOMOUS_TRANSACTION;
      owner varchar2(30);
      name varchar2(30);
      cursor cr_objs is select owner, name
         from sys.v_$db_object_cache
         where type in ('PACKAGE','PROCEDURE') and (loads > 1 or kept='YES');
    begin
      delete from sys.pin_objects;
      commit;
      open cr_objs;
      loop
        fetch cr_objs into owner, name;
        exit when cr_objs%notfound;
        insert into sys.pin_objects values (owner, name);
        commit;
      end loop;
    end;

step3、创建包，将pin_objects中的对象keep到shared pool
CREATE OR REPLACE PROCEDURE sys.sp_keep_objs AS
      owner varchar2(30);
      name varchar2(30);
      cursor cr_objs is select owner ,name from sys.pin_objects;
    BEGIN
      open cr_objs;
      loop
        fetch cr_objs into owner, name;
        exit when cr_objs%notfound;
        sys.dbms_shared_pool.keep(owner || '.' || name);
      end loop;
      sys.dbms_shared_pool.keep('SYS.STANDARD');
      sys.dbms_shared_pool.keep('SYS.DIUTIL');
   END;
   
step4、创建触发器
CREATE OR REPLACE TRIGGER db_list_trig
      BEFORE SHUTDOWN ON DATABASE
    BEGIN
      sys.sp_list_objs;
    END;
    CREATE OR REPLACE TRIGGER db_keep_trig
      AFTER STARTUP ON DATABASE
    BEGIN
      sys.sp_keep_objs;
    END; 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27119051/viewspace-2124464/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27119051/viewspace-2124464/