Oracle dbms_alert Utility

The dbms_alert Utility

The dbms_alertpackage was first available in Oracle 7 and is rarely used.  Oracle is such a vast and complicated set of software components that it is virtually impossible to stay current on all of its features and capabilities.  

The dbms_alert package provides an alerting mechanism based on events.  An event can be defined as an occurrence, or an action. Example events include the payroll table being updated, a specific user logging on to the database, or available free space less than 20 MB.  Anything that is detectable can be used to trigger an event.

The package has an asynchronous communication infrastructure but the Remote DBA uses the utility to assign messages their content. Remote DBAs can use it for database monitoring and application developers can use it to signal business or application events.

The dbms_alert package is created by executing the catproc.sql file and is owned by SYS.  Once granted the execute privilege to dbms_alert, it can be executed by any software component that can call a stored procedure including SQL*Plus, Java and Pro*C.

The dbms_alert package provides a mechanism for the database to notify a client (anything listening) of an event asynchronously, which means that the application does not need to periodically check for the occurrence of events.  Instead, when an event occurs, a notification will be sent.  In the past, developers created a polling process that checked the status of something on the database, like a completed job, by checking for a table value that the process had just updated. dbms_alert renders such techniques obsolete and is one of the best Oracle supplied packages. 

The dbms_alert package is even more helpful when dealing with 3 tier web applications – client, web server, and database.  Web applications are “stateless” by nature, meaning that the web server processes a request and it's done - there is no tethered connection like we're accustomed to with SQL*Plus, Oracle Applications, or SAP R/3.   dbms_alert provides a way for the database to initiate contact with the web server, who in turn can notify clients attached to it.