DBControl can do the same thing, but if you have more than one database on a server, this is where something like this will come in handy.
PLSQL setup, you should only have to change the SHOWARD part…
begin dbms_aqadm.grant_queue_privilege (privilege =>'DEQUEUE', queue_name => 'sys.alert_que', grantee => 'HOWARDS', grant_option=>FALSE); end; / grant execute on dbms_aq to howards; grant execute on sys.alert_type to howards; begin dbms_aqadm.create_aq_agent('SHOWARD'); dbms_aqadm.enable_db_access(agent_name => 'HOWARDS',db_username => 'HOWARDS'); end; / declare subscriber sys.aq$_agent; begin subscriber:=sys.aq$_agent('HOWARDS',null,null); dbms_aqadm.add_subscriber(queue_name => 'sys.alert_que', subscriber=>subscriber); end; / create or replace procedure howards.get_alerts (p_timestamp_originating out timestamp, p_reason_argument_1 out varchar2, p_reason_argument_2 out varchar2, p_reason_argument_3 out varchar2, p_reason_argument_4 out varchar2, p_reason_argument_5 out varchar2, p_instance_name out varchar2) is l_dequeue_options dbms_aq.dequeue_options_t; l_message_properties dbms_aq.message_properties_t; l_message_handle raw(16); l_event_msg sys.alert_type; begin l_dequeue_options.consumer_name := 'HOWARDS'; l_dequeue_options.visibility := dbms_aq.immediate; l_dequeue_options.dequeue_mode := dbms_aq.remove; dbms_aq.dequeue(queue_name => 'SYS.ALERT_QUE', dequeue_options => l_dequeue_options, message_properties => l_message_properties, payload => l_event_msg, msgid => l_message_handle); --dbms_output.put_line(l_event_msg.timestamp_originating || ' ' || l_event_msg.instance_name); p_timestamp_originating := l_event_msg.timestamp_originating; p_reason_argument_1 := l_event_msg.reason_argument_1; p_reason_argument_2 := l_event_msg.reason_argument_2; p_reason_argument_3 := l_event_msg.reason_argument_3; p_reason_argument_4 := l_event_msg.reason_argument_4; p_reason_argument_5 := l_event_msg.reason_argument_5; p_instance_name := l_event_msg.instance_name; commit; end; /
You can then register the procedure above as a callback with the queue, or for testing, run the python code below (still being fleshed out). You could also just run the get_alerts procedure (which will hang) and fill up a tablespace. You should see the alert output on the SQL*PLUS session.
#!/home/oracle/local/bin/python import cx_Oracle import string import time ora = cx_Oracle.Connection('howards/******@emprod1') curs = ora.cursor() n = curs.var(cx_Oracle.TIMESTAMP) o1 = curs.var(cx_Oracle.STRING) o2 = curs.var(cx_Oracle.STRING) o3 = curs.var(cx_Oracle.STRING) o4 = curs.var(cx_Oracle.STRING) o5 = curs.var(cx_Oracle.STRING) p = curs.var(cx_Oracle.STRING) while True: curs.callproc('get_alerts',[n,o1,o2,o3,o4,o5,p]) print str(n.getvalue()) + " " + str(o1.getvalue()) + " " + str(o2.getvalue()) + " " + str(o3.getvalue()) + " " + str(o4.getvalue()) + " " + str(o5.getvalue()) + " " + str(p.getvalue())
…which outputs stuff similar to…
12:08:21 oracle@emgrid01 ~ >./alerts.py 2010-01-19 12:11:58.848506 FOO 95 percent None None None emprod1 2010-01-19 12:21:58.917836 FOO 0 percent None None None emprod1 2010-01-19 12:41:59.033629 FOO 95 percent None None None emprod1
The PLSQL callback registration with the queue is much cleaner, as you would have to daemonize the python stuff above. When I get that completely working, I will post it.