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: Thanks I was able to easily purge the queue using dbms_aqadm.purge_queue_table
The SQL looks something like this :
-- purge queue
dbms_aqadm.purge_queue_table('MY_QUEUE_TABLE', NULL, po_t);
You can use the
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.
dbms_aq package. Check the example from the documentation: Dequeuing Messages.
Scroll down a little bit and there's a complete example.