Forum Stats

  • 3,769,715 Users
  • 2,253,014 Discussions
  • 7,875,158 Comments

Discussions

After dbms_aqadm.purge_queue_table, gv$aq still shows a lot of EXPIRED

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?