CSDN博客

img qqking

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

发表于2004/3/17 16:33:00  493人阅读

主题:本文说明在数据库启动的时候,如何自动获取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的存在。

0 0

相关博文

我的热门文章

img
取 消
img