主题：本文说明在数据库启动的时候，如何自动获取Shared Pool里最常用的过程和包等对象。
正文：
下面用实例来演示Startup之后和Shutdown之前，如何用Triger来完成自动管理的任务。
1.创建一个供Triger调用的Procedure
a．创建一个用来保存Procedure和Package的名称的Table（list_tab）
SQLcreate 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的存在。