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 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.
DBAs can use it for database monitoring and application developers can use it to signal business, or application events.
How It Works
The dbms_alert package begins with a client process registering interest in an alert. Once it registers for an alert, it waits for that specific alert (waitone ) or any other alert (waitany ) to occur. Once the alert occurs, the client is notified and a string is passed containing whatever the signal process selected to send to the client
Once the call to waitone or waitany is made, Oracle will check the dbms_alert_info table to see if any new rows have been inserted. If a new row exists, the data in the column message is returned from the procedure call.
The Java program below (Alert.java) can be executed from the DOS or UNIX prompt. This program prompts for database connection information, connects to the database via JDBC, registers for an alert, and waits for the LOGONALERT alert to occur. It can easily be customized to not only display an event but to prompt for the event of interest.
import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.driver.*;
public class Alert
{
static String host, dbname, port, user, password, option = null;
public static void main(String args[])
{
InputStreamReader isr = new InputStreamReader ( System.in );
BufferedReader br = new BufferedReader ( isr );
System.out.print(" ************************************************\n");
System.out.print(" * This is a simple utility to test *\n");
System.out.print(" * the alert notification between the database *\n");
System.out.print(" * and any client application *\n");
System.out.print(" ************************************************\n\n");
try {
System.out.print(" Enter Host Name: ");
host = br.readLine();
System.out.print("\n Enter Database Name: ");
dbname = br.readLine();
System.out.print("\n Enter TNS Listener Port#: ");
port = br.readLine();
System.out.print("\n Enter Database User Name: ");
user = br.readLine();
System.out.print("\n Enter Database User Password: ");
password = br.readLine();
System.out.print("\n Press
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection databaseConnection =
DriverManager.getConnection("jdbc:oracle:thin:@" +
host + ":" +
port + ":" +
dbname, user, password);
System.out.print("\n\nSuccessully connected to " + dbname + " as " + user);
System.out.print("\n");
String sql = null;
String message = null;
String status = null;
sql = "{call dbms_alert.register('LOGONALERT')}";
CallableStatement cs1 = databaseConnection.prepareCall(sql) ;
cs1.execute();
sql = "{call dbms_alert.waitone('LOGONALERT', ?, ?)}";
CallableStatement cs2 = databaseConnection.prepareCall(sql) ;
cs2.registerOutParameter(1, Types.VARCHAR);
cs2.registerOutParameter(2, Types.VARCHAR);
int x = 0;
while (x==0) {
cs2.execute();
String Result = cs2.getString(1);
System.out.print(Result + "\n");
}
databaseConnection.close();
} // try
catch (SQLException sqle) {
System.out.print("Unable to connect, error: " + sqle );
}
catch (IOException ioe) {
System.out.print("IO Exception (bad data): " + ioe );
}
}
}
Next, a trigger will be created that will signal this event. The signal code is the key to the trigger below. Notice that it contains the same alert name that the client registered, LOGONALERT.
dbms_alert.signal('LOGONALERT', ,
commit;
The following PL/SQL (Logontrigger.sql) is an AFTER LOGON TRIGGER that sends information to those registered for LOGONALERT. As part of the signal call, it returns the user, machine, and time that the user logged on. The commit statement after the signal call is required to send the signal.
CREATE or replace TRIGGER on_connect
AFTER LOGON ON DATABASE
DECLARE
pragma autonomous_transaction; -- needed for trigger commit
vsess varchar2(30);
vuser varchar2(30);
vmachine varchar2(30);
vosuser varchar2(30);
dbname varchar2(40);
BEGIN
select userenv('SESSIONID') into vsess from dual;
select username, machine, osuser
into vuser,vmachine,vosuser
from v$session where audsid = vsess;
select value
into dbname
from v$parameter
where name = 'db_name';
dbms_alert.signal('LOGONALERT', 'User: ' vuser
' logged on from ' vmachine
' at ' to_char(sysdate,'mm-dd-yyyy HH24:MI:SS'));
commit; -- required for the signal to work
END;
/
Once everything is configured, the trigger will fire when a user connects to the database. From the operating system, the program will be started and the required information entered.
C:\oracle9i\bin>java Alert
************************************************
* This is a simple utility to test *
* the alert notification between the database *
* and any client application *
************************************************
Enter Host Name: MoorePC
Enter Database Name: ORCL92
Enter TNS Listener Port#: 1521
Enter Database User Name: scott
Enter Database User Password: tiger
Press
Successully connected to ORCL92 as scott
User: SYS logged on from WORKGROUP\MOOREPC at 01-02-2003 21:18:37
SCOTT was the user that connected to the database to wait for the event. Within one second of user SCOTT logging in to the database, the LOGINALERT was signaled and sent to the Java client. Based on the message above, the SYS user logged in to the database.
Duplicating this Java code functionality in SQL*Plus would be simple, although not as functional, since SQL*Plus is not automatically notified of the event. The print statement below is used to check the status of the logon alert message.
SQL> var out1 varchar2(800)
SQL> var out2 varchar2(800)
SQL> exec dbms_alert.waitone('LOGONALERT', :out1, :out2);
PL/SQL procedure successfully completed.
SQL> print out1
OUT1
-------------------------------------------------------------------
User: SYS logged on from WORKGROUP\MOOREPC at 01-02-2003 21:24:55
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.
Practical Uses for dbms_alert
There are many times that users would prefer instant notification. For a DBA, the following events may prove worthy of real-time alerts:
· Unauthorized users logging on to database
· Unauthorized table access
· Performance measurements, including long wait events and locks
· Free space deficit
· Queries that have exceeded n seconds in CPU usage
The dbms_alert package is a powerful tool that allows asynchronous notification of events. Any event the DBA chooses can be monitored and communicated to any client application that registers an interest in the event. The dbms_alert package is a great tool that enhances the monitoring skills of the DBA.
-----------------------------------------------------------------------------------------------
Other Examples.
/* alert.sql */set serveroutput on;
declare
message varchar2(200);
status integer;
begin
dbms_alert.register('my_alert');
dbms_alert.waitone('my_alert',message,status,60);
dbms_output.put_line('status = 'status);
dbms_output.put_line('message = 'message);
dbms_alert.remove('my_alert');
end;
/
exit;
/* alert2.sql */
exec dbms_alert.signal('my_alert','a message from another process');
commit;
exit;
REM - alert.cmd (Windows NT/2000)
start cmd /k sqlplus system/manager @alertstart system/manager @alert2
#!/bin/sh# (for UNIX)
sqlplus system/manager @alert &
sqlplus system/manager @alert2
No comments:
Post a Comment