如何自动获取Oracle数据库启动时在Shared pool里面的对象（翻译）
2024-08-29 13:30:52
供稿：网友
主题：本文说明在数据库启动的时候，如何自动获取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的存在。
