oracle-databasequeuejmsoracle-aq

How to clear a queue in Oracle AQ

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

  • 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;