I have a queue table with this parameters:
queue_payload_type => 'RAW',
sort_list => 'PRIORITY,ENQ_TIME',
compatible => '8.1.3',
primary_instance => 0,
secondary_instance => 0
And a Queue in this queue table with this:
queue_type => sys.dbms_aqadm.normal_queue,
max_retries => 5,
retry_delay => 0,
retention_time => 0
I can see expired messages with this two options:
- select * from AQ$<QT_NAME> where MSG_STATE = 'EXPIRED';
- SELECT * FROM GV$AQ GV, all_queues AQ WHERE OWNER = USER AND GV.QID = AQ.QID AND AQ.QUEUE_TABLE='<QT_NAME>'
Then I execute this purge:
declare
po dbms_aqadm.aq$_purge_options_t;
lvszWhere VARCHAR2(1000);
begin
po.block := false;
lvszWhere := 'MSG_STATE = ''EXPIRED''';
dbms_aqadm.purge_queue_table(queue_table => 'QT_JOB_MESSAGETOTC',purge_condition =>
lvszWhere,purge_options => po);
end;
After excute the purge:
- I don't have rows in: select * from AQ$<QT_NAME> where MSG_STATE = 'EXPIRED'
- GV$AQ shows the same number of EXPIRED mesages.
Anyone having the same problem? Anyone can help me?