ASO_ORDER_FEEDBACK_T is huge !!!

Friends,
As part of regular house-keeping activities, we were checking for objects with huge sizes in our 11i10 apps instance and came across this object : ASO_ORDER_FEEDBACK_T

This is basically an “Order Capture” queue table that is maintained by Order Management. It works in a broadcast-subscribe concept. Order Management broadcasts evets (messages) via this queue and ANY (built-in or custom) application can subscribe to this queue to receive those messages.

In our environment, the table had millions of rows and was quite huge (12Gb). This was taking bulk of the space in the APPS_TS_QUEUES tablespace. When i was checking metalink to find ways to purge this queue, i came across this informative metalink note : 181410.1

In our case , we had OZF || READY || 4555658 which means only OZF module has subscribed to this queue and has NOT processed around 4.5 million records. We realized we maynot be using this and checked with the Functional group. Since they said NO, we thought of purging it.

How to purge?

Need to do 2 things.

1) Unsubscribe OZF module subscription. To do this, login to apps as “Quoting Sales Manager” resp and navigate as Setup -> Quick Codes -> Query for “ASO_ORDER_FEEDBACK_CRM_APPS”. You will see OZF’s subscription (result of above SQL query). Just disable / delete the record there.

2) Follow step 4 of the above metalink document to drop and recreate the queue.

As always, do this on a test environment , get a good functional (Order Mgmt,Shipping etc) flow testing done before thinking to do this in PROD.

We saved close to 12G. Perhaps, you could save more instead of adding another datafile to APPS_TS_QUEUES tablespace !!