熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

如何自動獲取Oracle數據庫啟動時在Shared pool裡面的對象

2013-11-13 15:48:28  來源: Oracle 

  主題本文說明在數據庫啟動的時候如何自動獲取Shared Pool裡最常用的過程和包等對象
  
  正文 下面用實例來演示Startup之後和Shutdown之前如何用Triger來完成自動管理的任務
  
  創建一個供Triger調用的Procedure
  
  a.創建一個用來保存Procedure和Package的名稱的Table(list_tab)
  
  SQL>create table syslist_tab (owner varchar()NAME VARCHAR());
  
  Table created
  
  b創建一個Procedure(proc_pkgs_list)來保存Shared Pool裡面的對象名
  
  SQL> create or replace PROCEDURE proc_pkgs_list AS
  
       own varchar();
  
       nam varchar();
  
     cursor pkgs is
  
       select ownername
  
       from SYSv_$db_object_cache
  
       where type in (PACKAGEPROCEDURE)
  
       and  (loads > or KEPT=YES);
  
   BEGIN
  
      delete from syslist_tab;
  
      commit;
  
       open pkgs;
  
       loop
  
       fetch pkgs into own nam;
  
       exit when pkgs%notfound;
  
      insert into syslist_tab values (own nam);
  
    commit;
  
    end loop;
  
         end;
  
         /
  
  Procedure created
  
  c創建Procedure(proc_pkgs_keep)用來保存調用dbms_shared_pool包的結果(注如果沒有dbms_shared_pool包可以用dbmspoolsql腳本生成)
  
  SQL> CREATE OR REPLACE PROCEDURE sysproc_pkgs_keep AS
  
    own varchar();
  
    nam varchar();
  
    cursor pkgs is
  
    select owner name
  
    from syslist_tab;
  
   BEGIN
  
    open pkgs;
  
    loop
  
    fetch pkgs into own nam;
  
    exit when pkgs%notfound;
  
    SYSdbms_shared_poolkeep(|| own || || nam || );
  
    end loop;
  
    sysdbms_shared_poolkeep(SYSSTANDARD);
  
    sysdbms_shared_poolkeep(SYSDIUTIL);
  
   END;
  
   /
  
  Procedure created
  
  .編譯測試Procedure
  
  SQL> execute sysproc_pkgs_list;
  
  PL/SQL procedure successfully completed
  
  SQL> execute sysproc_pkgs_keep;
  
  PL/SQL procedure successfully completed
  
  .創建Triger
  
  a. 在Instance shutdown之前的triger
  
  SQL> CREATE OR REPLACE TRIGGER db_shutdown_list
  
    BEFORE SHUTDOWN ON DATABASE
  
   BEGIN
  
    sysproc_pkgs_list;
  
   END;
  
   /
  
  Trigger created
  
  b. 在Instance startup之後的triger
  
  SQL> CREATE OR REPLACE TRIGGER db_startup_keep
  
    AFTER STARTUP ON DATABASE
  
   BEGIN
  
    sysproc_pkgs_keep;
  
   END;
  
   /
  
  Trigger created
  
  檢查alterlog文件查看Triger是否成功如果不成功則在數據庫關閉或者啟動的時候會看到如下提示*** SHUTDOWN
  
  Shutting down instance (immediate)
  
  License high water mark =
  
  Mon May ::
  
  ALTER DATABASE CLOSE NORMAL
  
  Mon May ::
  
  SMON: disabling tx recovery
  
  Mon May ::
  
  Errors in file /i/ora/admin/hp_/udump/ora_trc:
  
  ORA: trigger DB_SHUTDOWN_LIST is invalid and failed revalidation
  
  SMON: disabling cache recovery
  
  Mon May ::
  
  Thread closed at log sequence
  
  Mon May ::
  
  Completed: ALTER DATABASE CLOSE NORMAL
  
  Mon May ::
  
  ALTER DATABASE DISMOUNT
  
  Completed: ALTER DATABASE DISMOUNT
  
  *** STARTUP
  
  Example :
  
  Starting ORACLE instance (normal)
  
  LICENSE_MAX_SESSION =
  
  
  
  SMON: enabling tx recovery
  
  Tue Apr ::
  
  Errors in file /i/ora/admin/hp_/udump/ora_trc:
  
  ORA: trigger DB_STARTUP_KEEP is invalid and failed revalid
  
  ation
  
  Tue Apr ::
  
  Completed: alter database open
  
  Tue Apr ::
  
  Starting ORACLE instance (normal)
  
  LICENSE_MAX_SESSION =
  
  Example :
  
  SMON: enabling tx recovery
  
  Tue Apr ::
  
  Errors in file /i/ora/admin/hp_/udump/ora_trc:
  
  ORA: error occurred at recursive SQL level
  
  ORA: missing identifier
  
  ORA: at SYSDBMS_UTILITY line
  
  ORA: at SYSDBMS_SHARED_POOL line
  
  ORA: at SYSDBMS_SHARED_POOL line
  
  ORA: at SYSPROC_PKGS_KEEP line
  
  ORA: at line
  
  Tue Apr ::
  
  Completed: alter database open
  
  In the /i/ora/admin/hp_/udump/ora_trc file:
  
  Error in executing triggers on STARTUP
  
  ***
  
  ksedmp: internal or fatal error
  
  ORA: error occurred at recursive SQL level
  
  ORA: missing identifier
  
  ORA: at SYSDBMS_UTILITY line
  
  ORA: at SYSDBMS_SHARED_POOL line
  
  ORA: at SYSDBMS_SHARED_POOL line
  
  ORA: at SYSPROC_PKGS_KEEP line
  
  ORA: at line
  
  以上Procedure和Triger必須在sys的模式下執行並且保證表list_tab的存在
From:http://tw.wingwit.com/Article/program/Oracle/201311/17326.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.