如何自動獲取Oracle數(shù)據(jù)庫啟動時在Shared pool里面的對象（翻譯）
2024-08-29 13:49:05
供稿：網(wǎng)友
主題：本文說明在數(shù)據(jù)庫啟動的時候，如何自動獲取Shared Pool里最常用的過程和包等對象。
正文： 下面用實例來演示Startup之后和Shutdown之前，如何用Triger來完成自動治理的任務。
1.創(chuàng)建一個供Triger調(diào)用的PRocedure
a．創(chuàng)建一個用來保存Procedure和Package的名稱的Table（list_tab）
SQL>create table sys.list_tab (owner varchar2(64),NAME VARCHAR2(100));
Table created.
b.創(chuàng)建一個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.創(chuàng)建Procedure（proc_pkgs_keep）用來保存調(diào)用dbms_shared_pool包的結(jié)果（注：假如沒有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．創(chuàng)建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是否成功。
假如不成功，則在數(shù)據(jù)庫關(guān)閉或者啟動的時候會看到如下提示*** 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的模式下執(zhí)行，并且保證表list_tab的存在。
