Oracle 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
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.
To learn more about these techniques,
see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it directly from the
publisher and get instant access to the code
depot of utilities scripts.