Oracle PL/SQL Best Practices

Name

MOD-13: Implement server problem logs and “to do” lists using database triggers.

Synopsis

Oracle8i now offers database-level triggers that can be fired on events such as LOGON, LOGOFF, and SERVERERROR. These triggers offer all sorts of new possibilities for a DBA but can sometimes lead to problems.

Suppose, for instance, that an application encounters the ORA-01659 error (“unable to allocate next extent”). The solution is to add a data file to the applicable tablespace. With a SERVERERROR trigger, you can actually trap this problem and, right on the spot, add a data file. That’s all well and good, but while the data file is being added (which can take quite a while), the user process is blocked. Imagine a poor end user sitting at his terminal staring at a blank screen for 10 minutes while the database adds a dataflow to fix an error he wasn’t even aware had occurred.

A far superior approach to take is to first, adopt as a guiding principle that whenever possible the user process is allowed to continue uninterrupted. The SERVERERROR trigger should then simply logs an error or, even better, sends a message to a DBA (via the Oracle Advanced Queuing facility, for example) or builds a to-do list. This list can then be parsed and processed by a background database job run at regular intervals.

Database-level triggers fire as autonomous transactions, making it far easier to place an entry into a “to do” table, without affecting the user transaction.

Example

Here’s a simple SERVERERROR ...