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.<\/p>\n
PLSQL setup, you should only have to change the SHOWARD part\u2026<\/p>\n
\r\nbegin\r\n dbms_aqadm.grant_queue_privilege (privilege =>'DEQUEUE',\r\n queue_name => 'sys.alert_que',\r\n grantee => 'HOWARDS',\r\n grant_option=>FALSE);\r\nend;\r\n\/\r\n \r\ngrant execute on dbms_aq to howards;\r\ngrant execute on sys.alert_type to howards;\r\n \r\nbegin\r\n\u00a0 dbms_aqadm.create_aq_agent('SHOWARD');\r\n dbms_aqadm.enable_db_access(agent_name => 'HOWARDS',db_username => 'HOWARDS');\r\nend;\r\n\/\r\n \r\ndeclare\r\n subscriber sys.aq$_agent;\r\nbegin\r\n subscriber:=sys.aq$_agent('HOWARDS',null,null);\r\n dbms_aqadm.add_subscriber(queue_name => 'sys.alert_que', subscriber=>subscriber);\r\nend;\r\n\/\r\n \r\ncreate or replace procedure howards.get_alerts (p_timestamp_originating out timestamp,\r\n p_reason_argument_1 out varchar2,\r\n p_reason_argument_2 out varchar2,\r\n p_reason_argument_3 out varchar2,\r\n p_reason_argument_4 out varchar2,\r\n p_reason_argument_5 out varchar2,\r\n p_instance_name out varchar2) is\r\n l_dequeue_options dbms_aq.dequeue_options_t;\r\n l_message_properties dbms_aq.message_properties_t;\r\n l_message_handle raw(16);\r\n l_event_msg sys.alert_type;\r\nbegin\r\n l_dequeue_options.consumer_name := 'HOWARDS';\r\n l_dequeue_options.visibility := dbms_aq.immediate;\r\n l_dequeue_options.dequeue_mode := dbms_aq.remove;\r\n dbms_aq.dequeue(queue_name => 'SYS.ALERT_QUE',\r\n dequeue_options => l_dequeue_options,\r\n message_properties => l_message_properties,\r\n payload => l_event_msg,\r\n msgid => l_message_handle);\r\n --dbms_output.put_line(l_event_msg.timestamp_originating || ' ' || l_event_msg.instance_name);\r\n p_timestamp_originating := l_event_msg.timestamp_originating;\r\n p_reason_argument_1 := l_event_msg.reason_argument_1;\r\n p_reason_argument_2 := l_event_msg.reason_argument_2;\r\n p_reason_argument_3 := l_event_msg.reason_argument_3;\r\n p_reason_argument_4 := l_event_msg.reason_argument_4;\r\n p_reason_argument_5 := l_event_msg.reason_argument_5;\r\n p_instance_name := l_event_msg.instance_name;\r\n commit;\r\nend;\r\n\/\r\n<\/pre>\nYou 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.<\/p>\n
\r\n#!\/home\/oracle\/local\/bin\/python\r\n \r\nimport cx_Oracle\r\nimport string\r\nimport time\r\n \r\nora = cx_Oracle.Connection('howards\/******@emprod1')\r\ncurs = ora.cursor()\r\nn = curs.var(cx_Oracle.TIMESTAMP)\r\no1 = curs.var(cx_Oracle.STRING)\r\no2 = curs.var(cx_Oracle.STRING)\r\no3 = curs.var(cx_Oracle.STRING)\r\no4 = curs.var(cx_Oracle.STRING)\r\no5 = curs.var(cx_Oracle.STRING)\r\np = curs.var(cx_Oracle.STRING)\r\nwhile True:\r\n curs.callproc('get_alerts',[n,o1,o2,o3,o4,o5,p])\r\n print str(n.getvalue()) + \" \" + str(o1.getvalue()) + \" \" + str(o2.getvalue()) + \" \" + str(o3.getvalue()) + \" \" + str(o4.getvalue()) + \" \" + str(o5.getvalue()) + \" \" + str(p.getvalue())\r\n<\/pre>\n\u2026which outputs stuff similar to\u2026<\/p>\n
\r\n12:08:21 oracle@emgrid01 ~ >.\/alerts.py\r\n2010-01-19 12:11:58.848506 FOO 95 percent None None None emprod1\r\n2010-01-19 12:21:58.917836 FOO 0 percent None None None emprod1\r\n2010-01-19 12:41:59.033629 FOO 95 percent None None None emprod1\r\n<\/pre>\nThe 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2026 begin dbms_aqadm.grant_queue_privilege…<\/p>\n