How to clear a queue in Oracle AQ How to clear a queue in Oracle AQ oracle oracle

How to clear a queue in Oracle AQ


You can use the DBMS_aqadm.purge_queue_table procedure.


SOLUTION

The SQL looks something like this :

-- purge queueDECLARE po_t dbms_aqadm.aq$_purge_options_t;BEGIN  dbms_aqadm.purge_queue_table('MY_QUEUE_TABLE', NULL, po_t);END;


Just do a delete on the queue table.

Never mind, just did a check and that's not right:

Oracle Streams AQ does not support data manipulation language (DML) operations on queue tables or associated index-organized tables (IOTs), if any. The only supported means of modifying queue tables is through the supplied APIs. Queue tables and IOTs can become inconsistent and therefore effectively ruined, if DML operations are performed on them.


So, you'll have to create a little PL/SQL routine to pull the items off.

Use the dbms_aq package. Check the example from the documentation: Dequeuing Messages.Scroll down a little bit and there's a complete example.