如何自动获取Oracle数据库启动时在Shared pool里面的对象（翻译）

　　
主题：本文说明在数据库启动的时候，如何自动获取Shared Pool里最常用的过程和包等对象。

正文：下面用实例来演示Startup之后和Shutdown之前，如何用Triger来完成自动治理的任务。

1.创建一个供Triger调用的Procedure

a．创建一个用来保存Procedure和Package的名称的Table（list_tab）

    SQL>create table sys.list_tab (owner varchar2(64),NAME VARCHAR2(100));

   Table created.

    b.创建一个Procedure（proc_pkgs_list）来保存Shared Pool里面的对象名

SQL> create or replace PROCEDURE proc_pkgs_list AS

2                           own varchar2(64);

     3         nam varchar2(100);

     4      cursor pkgs is

     5         select owner,name

     6         from SYS.v_$db_object_cache

     7         where type in ('PACKAGE','PROCEDURE')

     8         and   (loads > 1 or KEPT='YES');

     9 BEGIN

    10        delete from sys.list_tab;

    11        commit;

    12         open pkgs;

    13         loop

    14         fetch pkgs into own, nam;

    15         exit when pkgs%notfound;

    16       insert into sys.list_tab values (own , nam);

    17    commit;

    18    end loop;

19             end;

20             /

    Procedure created.

c.创建Procedure（proc_pkgs_keep）用来保存调用dbms_shared_pool包的结果（注：假如没有dbms_shared_pool包，可以用dbmspool.sql脚本生成）

   SQL> CREATE OR REPLACE PROCEDURE sys.proc_pkgs_keep AS

     2   own varchar2(64);

     3   nam varchar2(100);

     4   cursor pkgs is

     5   select owner ,name

     6   from sys.list_tab;

     7 BEGIN

     8   open pkgs;

     9   loop

    10   fetch pkgs into own, nam;

    11   exit when pkgs%notfound;

    12   SYS.dbms_shared_pool.keep('' own '.' nam '');

    13   end loop;

    14   sys.dbms_shared_pool.keep('SYS.STANDARD');

    15   sys.dbms_shared_pool.keep('SYS.DIUTIL');

    16 END;

    17 /

   Procedure created.

2．编译、测试Procedure

   SQL> execute sys.proc_pkgs_list;

   PL/SQL procedure sUCcessfully completed.

   SQL> execute sys.proc_pkgs_keep;

   PL/SQL procedure successfully completed.

3．创建Triger

a．在Instance shutdown之前的triger

   SQL> CREATE OR REPLACE TRIGGER db_shutdown_list

     2   BEFORE SHUTDOWN ON DATABASE

     3 BEGIN

     4   sys.proc_pkgs_list;

     5 END;

     6 /

   Trigger created.

b．在Instance startup之后的triger

   SQL> CREATE OR REPLACE TRIGGER db_startup_keep

     2   AFTER STARTUP ON DATABASE

     3 BEGIN

     4   sys.proc_pkgs_keep;

     5 END;

     6 /

   Trigger created.

检查alter.log文件，查看Triger是否成功。
假如不成功，则在数据库关闭或者启动的时候会看到如下提示*** SHUTDOWN

Shutting down instance (immediate)

License high water mark = 2

Mon May 22 12:31:45 2000

ALTER DATABASE CLOSE NORMAL

Mon May 22 12:31:45 2000

SMON: disabling tx recovery

Mon May 22 12:31:46 2000

Errors in file /8i/ora815/admin/hp11_815/udump/ora_12624.trc:

ORA-04098: trigger 'DB_SHUTDOWN_LIST' is invalid and failed re-validation

SMON: disabling cache recovery

Mon May 22 12:31:47 2000

Thread 1 closed at log sequence 16579

Mon May 22 12:31:47 2000

Completed: ALTER DATABASE CLOSE NORMAL

Mon May 22 12:31:47 2000

ALTER DATABASE DISMOUNT

Completed: ALTER DATABASE DISMOUNT

*** STARTUP

Example 1:

Starting Oracle instance (normal)

LICENSE_MAX_SESSION = 0

...

SMON: enabling tx recovery

Tue Apr 18 10:21:38 2000

Errors in file /8i/ora815/admin/hp11_815/udump/ora_7291.trc:

ORA-04098: trigger 'DB_STARTUP_KEEP' is invalid and failed re-valid

ation

Tue Apr 18 10:21:38 2000

Completed: alter database open

Tue Apr 18 10:21:30 2000

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

Example 2:

SMON: enabling tx recovery

Tue Apr 18 11:12:41 2000

Errors in file /8i/ora815/admin/hp11_815/udump/ora_7562.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00931: missing identifier

ORA-06512: at "SYS.DBMS_UTILITY", line 68

ORA-06512: at "SYS.DBMS_SHARED_POOL", line 43

ORA-06512: at "SYS.DBMS_SHARED_POOL", line 51

ORA-06512: at "SYS.PROC_PKGS_KEEP", line 13

ORA-06512: at line 2

Tue Apr 18 11:12:41 2000

Completed: alter database open

In the /8i/ora815/admin/hp11_815/udump/ora_7562.trc file:

Error in executing triggers on STARTUP

*** 2000.04.18.11.12.41.052

ksedmp: internal or fatal error

ORA-00604: error occurred at recursive SQL level 1

ORA-00931: missing identifier

ORA-06512: at "SYS.DBMS_UTILITY", line 68

ORA-06512: at "SYS.DBMS_SHARED_POOL", line 43

ORA-06512: at "SYS.DBMS_SHARED_POOL", line 51

ORA-06512: at "SYS.PROC_PKGS_KEEP", line 13

ORA-06512: at line 2

以上Procedure和Triger必须在sys的模式下执行，并且保证表list_tab的存在。