Inter-process communication with DBMS_ALERT
March 17, 2009
DBMS_ALERT may not be the most elegant way to communicate between processes but it does provide a quick and easy method.
Most of what Ive read on the Net regarding DBMS_ALERT had to do with applications using this package to signal one another when something had changed in the database. More specifically, assume you had a report that needed to be generated when table data changed. If you used old-school techniques to do this, you would schedule a cron or AT job, depending on your operating system, that would check to see if new data was inserted, changed, or deleted and then the scheduled job would kick off a new report. The problem with this approach would be the extra overhead needed to query the database every time to see if data in the tables supporting the report had changedcompletely unwanted.
For the same scenario above, if we use DBMS_ALERT, the processes that generate the report and the processes that modify table data could communicate with each other and agree upon the timing of new report generation. For instance, what if the report was data sensitive on load patterns? A batch job could effectively modified table data and signal to the reporting function at those data sensitive times when a new report should be generatedputting the load process on hold until the report process signaled back that a report was successfully generated and loading could continue.
The reporting scenario is just one where inter-process communication with DBMS_ALERT makes sense. Below Ive created a set of time sensitive actions that help clarify how this might work with DBMS_ALERT communicating between two SQLPlus sessions. In the example, I wanted to create a scenario where all logon requests to the database had to go through an administrator console. In the table below, the Action column describes briefly what will be done or experienced, Session #1 is considered the administrator console session, and Session #2 is considered the user wishing to connect to the database.
To step through this example:
1. Logon to the database as a privileged user and create the LOGON_TRIGGER. Every user that attempts a logon will execute this trigger. Realizing this trigger will execute for privileged users (sys, system, DBAs, etc.) if you ever wanted to really use this, you would more than likely put in some traps to evaluate the user and perform different actions.
a. The key to the logon trigger is the DBMS_ALERT package that sends a SIGNAL to an administrator (procedure in next step) telling him that the user would like to logon. This is done through the logon_request alert.
b. The logon session then REGISTERs with an alert of interest, the logon_wait alert.
c. The logon session then waits (WAITONE) for a message on the logon_wait alert. Do notice that there has been a 60-second timeout assigned to this wait. After the 60 seconds, the user will automatically execute through the logon trigger. This was done, since it is a logon trigger, so that access to the database would not be shutdown for everyone. Id hate to have a timeout of 86400, along with a bug in the code, and then no one could login for 24 hours. Modify the code cautiously if you intend to use it, I dont want to get an email.
d. The logon session then disables (REMOVES) itself from any notification on the alert
2. For the administrative console(s), execute the following code to wait for logon requests. This will effectively make your session wait for a message.
a. The session first REGISTERs with an alert of interest, the logon_request.
b. Next the session will then wait (WAITONE) for a message on the logon_request alert. Again, note that I have put a 60-second limit on this wait before it times out. Changing this timeout is not as critical as the timeout within the LOGON_TRIGGER as you can always kill this session if needed.
c. The DBMS_OUTPUT.PUT_LINEs will display a message on the administrators console when someone sends a message from the LOGON_TRIGGER (DBMS_ALERT.SIGNAL).
d. After receiving a signal from a logon session the administrator console will disable (REMOVE) itself from any notification on the alert logon_request.
3. When a session tries to logon, with sqlplus in this case, the SQLPlus logon session will SIGNAL the administrator console (through the LOGON_TRIGGER) and then hang (60 seconds).
4. The administrator console session will receive a set of messages asking for logon permission. Notice that my O/S user was oracle.
5. After the logon request is sent to the administrator the SQLPlus session is hung or will be waiting for a subsequent alert notification. The administrator SIGNALS back that the requester is good to continue.
6. Session #2 gets the SQL>; prompt
The example itself is somewhat rudimentary as there is no sending or checking of statuses, no checking of message content, and it could benefit from some looping involved for the administrator console or a GUI front end that would subsequently iterate through requests. Adding these extra features could build this out into a nice security front end for various logon requests. Nevertheless, hopefully youve gained a bit more appreciation for the DBMS_ALERT package if you havent already. This package has been around since version 8 and I venture to say that many havent even heard of it or played around with its potential.
Many times, we wonder how to schedule jobs, halt, suspend, and continue given certain conditions within the database or application runtime statuses. Making use of DBMS_ALERT has the potential to fill those voids without the use of crontab, AT, or some other elaborate scheduling mechanism.