I would like to transfer the enqueued messages to a backup table after they have been dequeued.

Currently we are using DBMS_AQ.DEQUEUE with mode dequeue_mode := DBMS_AQ.REMOVE.

So after the message gets dequeued, the queue table does not keep any entry. But we need the message in a backup table once dequeued, so that we can check the backup table for debugging purposes.

If I use browse mode, would it help ? Are there any other alternatives?

I am using Oracle 11g.


I see two options:

1) use DBMS_AQADM.ALTER_QUEUE to change the retention of the queue. This way, when you REMOVE a message, it still stays in the queue table with a status of PROCESSED. You can specify the retention period. The table remains fast even with these messages there. When you are done, you can either manually delete the rows or let the database clean it up when the retention period has expired.

2) I'm not sure I would recommend this, but I've seen people put a ROW-DELETE trigger on the queue table for this purpose.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.