Topic: This article describes how to automatically get the most commonly used processes and packages in Shared Pool when they start.
Text: How to demonstrate STARTUP with instances and Shutdown to complete automatic management tasks before STARTUP.
1. Create a procedure for Triger calls
a. Create a table for saving the name of Procedure and Package (List_Tab)
SQL> CREATE TABLE SYS.LIST_TAB (Owner Varchar2 (64), Name Varchar2 (100));
Table created.
b. Create a procedure (proc_pkgs_list) to save the object name inside 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 create.
c. Creating procedure (proc_pkgs_keep) to save the result of calling the dbms_shared_pool package (Note: If there is no dbms_shared_pool package, you can generate with dbmspool.sql script)
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 create.
2. Compile, test procedureSQL> execute sys.proc_pkgs_list;
PL / SQL Procedure SuccessFully Completed.
SQL> EXECUTE SYS.PROC_PKGS_KEEP;
PL / SQL Procedure SuccessFully Completed.
3. Create Triger
a. Triger before Instance Shutdown
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. Triger after Instance Startup
SQL> Create or Replace Trigger DB_STARTUP_KEEP
2After Startup on Database
3 Begin
4 sys.proc_pkgs_keep;
5 End;
6 /
Trigger created.
Check if the ALTER.LOG file is to see if TriGer is successful. If it is not successful, you will see the following prompt when the database is turned off or started. *** 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 Dismbount
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
attempt
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 2000ERRORS IN FILE / 8I / ORA815/Admin/HP11_815/UDUMP/Or_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
The above Procedure and TriGer must be executed in SYS mode and ensure that the presence of table List_Tabs.
