问题描述:

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

DECLARE

po_t dbms_aqadm.aq$_purge_options_t;

BEGIN

dbms_aqadm.purge_queue_table('MY_QUEUE_TABLE', NULL, po_t);

END;

网友答案:

You can use the DBMS_aqadm.purge_queue_table procedure.

网友答案:

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.

相关阅读:
Top