主题：本文说明在数据库启动的时候，如何自动获取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的存在。
查看本文来源
京公网安备 11010802021500号