Issue:
dequeue一个queue的时候,永远的hang在那里,等待事件是cache buffer chain. 即使只dequeue一个message,也是同样的问题。
Solution:
(1) 检查一个与queue table相关的IOT: AQ$_Queue_Table_Name_I:
select distinct SUBSCRIBER#, NAME, QUEUE# from AQ$_Queue_Table_Name_I ;
Oracle的解释:
When messages are enqueued for an existing subscriber, the values of column NAME and QUEUE# are expected to be 0. If the value of the two columns are not 0, just change them to 0 and try again.
(2) 如果不是上面的情况. 只能purge:
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table IN VARCHAR2,
purge_condition IN VARCHAR2,
purge_options IN aq$_purge_options_t);
purge_condition: 是一个where子句,基于aq$queue_table_name的列. 并且所有的列名前面必须加“qtview.” 如果要purge整个queue table,设置为NULL。
Purge_options 有个参数“block”. True 表示在purge的时候会给queue table加锁来保证purge的成功. False 表示没有锁,在高并发环境下purge可能失败。
Example1: Purging one queue
DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := TRUE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'test.obj_qtab',
purge_condition => 'qtview.queue = ''TEST.OBJ_QUEUE''',
purge_options => po);
END;
/
Example2: Purging the whole queue table
DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'test.obj_qtab',
purge_condition => NULL,
purge_options => po);
END;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8684388/viewspace-619973/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8684388/viewspace-619973/