如何自動獲取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的存在。
