Donald K. Burleson    Oracle Utilities Tips

The dbms_alert_info Table

Any alert that has been registered via dbms_alert.register is inserted into the dbms_alert_info table. This table is owned by the SYS user and contains the data for registered alerts.  A DBA can query this table to discover who is waiting for certain events.  This can be important since the DBA may want to inspect the types of alerts that are being generated.  In addition, in the event that the database needs restarting, the DBA can see the sessions waiting on events that will be impacted. 

The columns of the dbms_alert_info table include:  

·     Name - The name of the alert specified by the user. 

·     SID – Session ID of the session that is waiting for this alert.

·     Changed – Y/N indicator that the alert has been signaled.  Y = alert signaled, N = no alert signaled via (dbms_alert.SIGNAL)

·     Message – An 800-byte character string that can be passed to the client waiting for the alert.  An 800-byte character is large enough to send a meaningful message to any registered client.  This message is optional, since the occurrence of a specific alert may be enough information alone.
 

dbms_alert Methods

The following options are available when utilizing dbms_alert.  The register and signal procedures are required, the rest are optional.  

·     register – The register procedure adds a session and specified alert to a registration list that appears in the dbms_alert_info table.  There is no limit to the number of alerts a session can register.  When the session is no longer interested in receiving alerts, the remove procedure (or remove_all) should be called to deactivate the notifications.  Register always performs a commit on exit.

·     remove – Procedure that removes the specified alert from the registration list for a particular session.  This should always be called when the session is no longer interested in receiving particular alerts.

·     remove_all – Procedure that removes all of the registered events for that session.   

·     set_defaults – This procedure sets the default polling interval, in seconds.

·     signal - The signal procedure sends an alert to any session that has registered for it.  The signal includes the name of the alert along with an optional 800-character string.  This string negates the need for client sessions to ping the database for information.  The SIGNAL only works after a commit is issued.   

·     waitany – In the event that a session has registered interest in many alerts, the waitany procedure is used for the client to be notified if any of them occur.  Waitany avoids the need to implement multiple waitone calls, thereby simplifying the code. When waitany is called, the timeout period in seconds can be specified.

·     waitone – This procedure waits for one alert to occur as specified in the call to waitone.