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