This content has been marked as final. Show 2 replies
I'm in same position as you. Millions of records in WF_ERROR and WF_DEFERRED. And no apparent way to easily remove rows.
Did you end up truncating these tables in Production? Any issues when you did so?
WF_ERROR queue is used for error reporting purposes. You can rebuilt the queue table and get away with it, but any failures that occurred and have not been notified about could be missed.
WF_DEFERRED is a functional queue and it contains messages for applications all around, of any type. Messing with this queue is very advised against. It cannot be recovered but from a backup. You want this queue to be successfully dequeued and processed by the Deferred Agent Listener.
Now, as with any queue in Advance Queuging (Streams) you can check the property RETENTION_TIME of the queue table. When a message is processed and no longer needed, the message will be there for as many seconds as this column reads. Check DBA_QUEUES.RETENTION_TIME. Usually this value should be zero but sometimes can be increased so as to let messages sit there for a while and run debug activities. If no issues are present then the retention time should be zero and messages should be removed as soon as they are processed.
Please check dbms_aqadm package to see how to change the retention_time.
Lastly, check what the status of those messages are. Ideally you should only have status=0 (ready to be processed by the next loop of the agent listener) or 2 (processed). Large amounts of messages in other states are to be investigated.