Donald K. Burleson Oracle Tips

Oracle8i system-level triggers

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 DML event, system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror triggers:

• 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.

• 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.

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

• 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.

• 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.

To see how system-level triggers work, let’s look at creating a DDL trigger.  The DDL trigger executes every time a DDL statement is executed, and adds new entries to the stats$ddl_log table.

connect sys/manager

create or replace trigger

   DDLTrigger

AFTER DDL ON DATABASE

BEGIN

insert into

   perfstat.stats$ddl_log

(

   user_name,

   ddl_date,

   ddl_type,

   object_type,

   owner,

   object_name

)

VALUES

(

   ora_login_user,

   sysdate,

   ora_sysevent,

   ora_dict_obj_type,

   ora_dict_obj_owner,

   ora_dict_obj_name

);

END;

/

In this example, every DDL event is logged into the stats$ddl_log table.  From this table, you can create easy audit reports that show every database change in your production environment.

                Production Database Changes

                     Summary DDL Report

                   Changed                Number of        

DDL_D USER_NAME    Object             Production Changes

----- -----------  ---------------       ----------        

07-21 WCEDI        PACKAGE                   6        

      WCEDI        PACKAGE BODY              6        

*****                                    ----------        

sum                                         12        

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: