Keeping messages in a backup table after dequeue is completed

oracleoracle-11goracle-streams

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.

Best Answer

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.

Related Question