Oracle SQL Cost-based Optimizer Database Event Triggers

You can now write database triggers that fire when a user logs on or logs off of Oracle. The following example fires whenever someone connects as the Oracle user SYSTEM. Database triggers now permit the COMMIT statement:

SQL> SHOW USER

USER is "SYS"

The following Oracle8i event trigger fires whenever someone logs on as SYSTEM. The trigger inserts a row into the table sys.event_log, which is created manually by the database administrator.

SQL> CREATE TABLE EVENT_LOG(WHO_AND_WHEN VARCHAR2(70));

Table Created.

SQL> CREATE OR REPLACE TRIGGER track_logons

  2         AFTER LOGON ON DATABASE

  3         WHEN (user = 'SYSTEM')

  4  BEGIN

  5    INSERT INTO sys.event_log

  6      VALUES(user || ' Logged On At ' || to_char(sysdate,

  7        'MM/DD/YYYY HH:MI:SS'));

  8    COMMIT;— Can't Do This In Previous Versions

  9* END;

SQL> /

Trigger created.

So that you can see the time on the terminal while in SQL*Plus, set the time environmental parameter on.

SQL> SHOW TIME

time OFF

Notice that someone connects as SYSTEM at 5:49 PM or 17:49 military time.

SQL> SET TIME ON

17:49:30 SQL> CONNECT SYSTEM/MANAGER

Connected.

17:49:41 SQL> SET TIME OFF

Write a query using sys.event_log to test your event trigger tracking log-ons for user SYSTEM. As you can see, your trigger works.

SQL> SELECT *

  2  FROM   SYS.EVENT_LOG;

WHO_AND_WHEN

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

SYSTEM Logged On At 01/12/2000 05:49:41

To track who drops an object and when, the name of the object and its type, write an event trigger such as the following. First, create a table to contain the required information gleaned from the trigger:

SQL> CREATE TABLE who_dropped_object

  2              (who_done_it VARCHAR2(30),

  3               when_dropped DATE,

  4               obj_name VARCHAR2(30),

  5               obj_type VARCHAR2(30))

  5* TABLESPACE users;

Table created.

Use the Oracle provided dbms_standardpackage to determine which user dropped an object, the name of the object, and the object type.

SQL> CREATE OR REPLACE TRIGGER track_dropped_ objects

  2         AFTER      DROP

  3         ON         DATABASE—Not Schema But Database

  4  BEGIN

  5    INSERT into who_dropped_object

  6    VALUES(dbms_standard.login_user, sysdate,

  7           dbms_standard.dictionary_ obj_name,

  8           dbms_standard.dictionary_ obj_type);

  9* END;

SQL>/

Trigger created.

Write a query using dba_triggersto display infor- mation about the database triggers in your database.

SQL> DESC DBA_TRIGGERS

Name            Null? Type

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

OWNER                 VARCHAR2(30)

TRIGGER_NAME          VARCHAR2(30)

TRIGGER_TYPE          VARCHAR2(16)

TRIGGERING_EVENT      VARCHAR2(75)

TABLE_OWNER           VARCHAR2(30)

BASE_OBJECT_TYPE      VARCHAR2(16)

TABLE_NAME            VARCHAR2(30)

COLUMN_NAME         VARCHAR2(4000)

REFERENCING_NAMES    VARCHAR2(128)

WHEN_CLAUSE         VARCHAR2(4000)

STATUS                 VARCHAR2(8)

DESCRIPTION         VARCHAR2(4000)

ACTION_TYPE           VARCHAR2(11)

TRIGGER_BODY                  LONG

This query indicates an event trigger associated with the database and not just a specific schema.

SQL> SELECT BASE_OBJECT_TYPE,— Database or Schema

  2         TRIGGER_TYPE,— Before or After

  3         TABLE_NAME— Null For Db and Schema

  4  FROM   DBA_TRIGGERS

  5  WHERE  TRIGGER_NAME = 'TRACK_DROPPED_OBJECTS';

BASE_OBJECT_TYPE TRIGGER_TYPE TABLE_NAME

================ ============ ==========

DATABASE         AFTER EVENT

User NOT_TOO_BRIGHT connects to Oracle and drops the table SYSTEM.PARTS.

SQL> CONNECT NOT_TOO_BRIGHT/NOT_TOO_BRIGHT

Connected.

SQL> DROP TABLE SYSTEM.PARTS;

Table dropped.

Applications are failing because a production table is not in the database. So, you connect to Oracle to determine who dropped the PARTS table.

SQL> CONNECT SYSTEM/MANAGER

Connected.

SQL> COL WHO_DONE_IT  FORMAT A14

SQL> COL WHEN_DROPPED FORMAT A12

SQL> COL OBJ_NAME     FORMAT A8

SQL> COL OBJ_TYPE     FORMAT A8

You can see that user NOT_TOO_BRIGHT is the guilty party.

SQL> SELECT *

  2* FROM   WHO_DROPPED_OBJECT;

WHO_DONE_IT    WHEN_DROPPED OBJ_NAME OBJ_TYPE

============== ============ ======== ========

NOT_TOO_BRIGHT 19-APR-00    PARTS    TABLE

You can also write event triggers to track when an Oracle database is shut down and when it is started. The following event trigger fires when your Oracle database is shut down:

SQL> CREATE TABLE shutdowns

  2              (who_shutdown  VARCHAR2(30)

  3               when_shutdown DATE)

  4* TABLESPACE   users;

Table created.

If you use AFTER SHUTDOWN, your trigger does not compile. You must use BEFORE SHUTDOWN:

SQL> CREATE OR REPLACE TRIGGER track_shutdowns

  2         BEFORE     SHUTDOWN — after won't work

  3         ON         DATABASE

  4    BEGIN

  5    INSERT into shutdowns

  6    VALUES(dbms_standard.login_user, sysdate);

  7* END;

SQL>/

Trigger created.

To test your event trigger, shut down your database.

SQL> SHUTDOWN;

Database closed.

Database dismounted.

ORACLE instance shut down.

Now, start up your database.

SQL>CONNECT INTERNAL/ORACLE

Connected to an idle instance.

SQL>STARTUP;

ORACLE instance started.

Total System Global Area  4.3E+07 bytes

Fixed Size                  65484 bytes

Variable Size             1.3E+07 bytes

Database Buffers          3.1E+07 bytes

Redo Buffers                73728 bytes

Database mounted.

Database opened.

To verify your event trigger fired correctly, write a query using the table you created to contain shutdown information. There were two shutdowns on your database today, three minutes apart.

SQL> COL WHEN_SHUTDOWN FORMAT A13

SQL> SELECT WHO_SHUTDOWN,

  2         TO_CHAR(WHEN_SHUTDOWN,'MM/DD HH:MI:SS') WHEN

  3  FROM   SHUTDOWNS;

WHO_SHUTDOWN             WHEN

=======================  ==============

SYSTEM                   04/19 04:48:44

---

The above text is an excerpt from: Oracle SQL Tuning & CBO Internals ISBN 0-9745993-3-6 by Kimberly Floss