科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道如何自动获取Oracle数据库启动时在Shared pool里面的对象

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

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

  主题:本文说明在数据库启动的时候,如何自动获取Shared Pool里最常用的过程和包等对象。      正文: 下面用实例来演示Startup之后和Shutdown之前,如何用Triger来完成自动管理的任务。

作者:中国IT实验室 来源:中国IT实验室 2007年9月15日

关键字: 数据库 ORACLE

  • 评论
  • 分享微博
  • 分享邮件

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

查看本文来源

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章