如何自动获取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 suclearcase/" target="_blank" >ccessfully 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的存在。

原文转自：http://www.ltesting.net