oracle数据库触发器怎么重启,数据库启动的时候触发一个触发器？？

Extending Oracle for System Event Auditing

by Donald K. Burleson

Some of the most exciting new features within Oracle are the new system-level triggers that were introduced in Oracle8i. What is truly exciting is that we can combine the system-level triggers with STATSPACK extension tables, thereby building a mechanism that will give complete auditing information on user logon and logoff, DDL, and server errors.

Just as an Oracle trigger fires on a specific DM event, system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror triggers:

1. Database startup triggers - Database startup triggers can be used to invoke the dbms_shared_pool.keep procedure to pin all frequently-referenced PL/SQL packages into RAM.

2. logon triggers - The logon triggers can be used to store login information directly inside Oracle tables, thereby providing a complete audit of all those times when users entered your system.

3. logoff triggers -- Oracle logoff triggers can automatically capture elapsed session duration times for all users who have accessed your Oracle database.

4. Servererror triggers -- With the servererror trigger, the Oracle administrator can automatically log all serious PL/SQL errors into an Oracle STATSPACK extension table. This table, in turn, has an insert trigger that e-mails the server error directly to the Oracle professional for immediate notification.

5. DDL triggers -- Using the DDL trigger, the Oracle administrator can automatically track all changes to the database including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.

Now let's take a close look at how these triggers work with Oracle tables.

Database Startup Event Triggers

Package pinning has become a very important part of Oracle tuning, and with the introduction of system-level triggers in Oracle8i, we now have an easy tool to ensure that frequently-executed PL/SQL remains cached inside the shared pool.

Just like using the KEEP pool with the data buffer caches, pinning packages ensures that the specified package always remains in the Most Recently Used (MRU) end of the data buffer. This prevents the PL/SQL from being paged-out, and then re-parsed on reload. The Oracle DBA controls the size of this RAM region by setting the shared_pool_size parameter to a value large enough to hold all of the PL/SQL.

Pinning of packages involves two areas:

1 - Pinning frequently executed packages -- Oracle performance can be greatly enhanced by pinning frequently executed packages inside the SGA.

2 - Pinning the standard Oracle packages -- These are shown in the code listing below, and should always be opined to prevent re-parsing by the Oracle SGA.

You can interrogate the v$db_object_cache view to see the most frequently used packages, and automatically pin them at database startup time (with an ON DATABASE STARTUP trigger) using dbms_shared_pool.keep.

create or replace trigger

pin_packs

after startup on database

begin

-- Application-specific packages

execute dbms_shared_pool.keep('MAIN_PACK');

execute dbms_shared_pool.keep('OTHER_PACK');

-- Oracle-supplied software packages

execute dbms_shared_pool.keep('DBMS_ALERT');

execute dbms_shared_pool.keep('DBMS_DDL');

execute dbms_shared_pool.keep('DBMS_DESCRIBE');

execute dbms_shared_pool.keep('DBMS_LOCK');

execute dbms_shared_pool.keep('DBMS_OUTPUT');

execute dbms_shared_pool.keep('DBMS_PIPE');

execute dbms_shared_pool.keep('DBMS_SESSION');

execute dbms_shared_pool.keep('DBMS_STANDARD');

execute dbms_shared_pool.keep('DBMS_UTILITY');

execute dbms_shared_pool.keep('STANDARD');

end;