How to clear a queue in Oracle AQ

Question

I've been testing Oracle AQ for the first time. I have managed to create 2000 rows of test inserts into the queue I created.

Now, I'd like to clear those out. As I was teaching myself, I set the expiry time to be a month. I can't wait that long. And I don't think I should just delete them from the queue table.

What's the best way to do this?

Solution 1

You can use the DBMS_aqadm.purge_queue_table procedure.

---

SOLUTION

The SQL looks something like this :

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

Solution 2

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.

Comments

[ono2012]

This solution looks exactly like the example here: psoug.org/reference/dbms_aqadm.html. For those who like Oracle docs docs.oracle.com/cd/B19306_01/server.102/b14257/… see examples 8-17 and 8-18 "Purging All Messages in a Queue Table"

[jeph perro]

I knew I couldn't delete from the queue table. What API do I use to remove a message from the queue?

[Ev.Rei.]

Worked perfect! Thanks a lot