Only Four Left…

There, got that out of my system. I really shouldn't write blog entries after reading The Onion.

A post today on Oracle Mix prompted me to dust off some notes about a problem I ran into a while ago with a table growing out of control in E-Business Suite. Here it is, rescued from the "Hey, that could be a blog post someday" bin:

Observations

1. The APPS_TS_QUEUES tablespace in our production E-Business Suite instance had grown to the point where it was a significant percentage of the database's overall storage footprint. Not huge, but big enough to be noticeable.
2. Most of the space seemed to be consumed by the table ASO_ORDER_FEEDBACK_T and its related LOB segments:

   select segment_name "Segment",
   tablespace_name,
   segment_type "Type",
   bytes/1024/1024 "MB"
   from dba_segments
   where segment_name like 'ASO_%FEEDBACK%';
   
   Segment              TABLESPACE_NAME Type  MB
   -------------------- --------------- ----- -----
   ASO_ORDER_FEEDBACK_T APPS_TS_TX_DATA TABLE 3001
   
   select sum(s.bytes)/1024/1024 "MB", count(1) "NumLobs"
   from dba_lobs l, dba_segments s
   where s.segment_name = l.segment_name
   and l.table_name = 'ASO_ORDER_FEEDBACK_T';
   
   MB    NumLobs
   ----- ----------------------
   520   26
   

3. It appears that all space in ASO_ORDER_FEEDBACK_T is taken up with unprocessed records for the Trade Management (OZF) consumer:

   select consumer_name, msg_state, count(*)
   from aso.aq$aso_order_feedback_t
   group by consumer_name, msg_state;
   
   CONSUMER_NAME MSG_STATE COUNT(*)
   ------------- --------- ----------------------
   OZF           READY     468562
   

4. On our system, Trade Management (OZF) wasn't even licensed, which probably accounts for the records not being consumed from the queue:

   select app.product_code,
   tl.application_name,
   decode(inst.status, 'I', 'Installed',
   'S', 'Shared',
   'N', 'Not Installed',
   'Wha-huh?') LicStatus,
   inst.patch_level
   from applsys.fnd_product_installations inst,
   applsys.fnd_application app,
   applsys.fnd_application_tl tl
   where app.application_id= inst.application_id
   and tl.application_id= app.application_id
   and app.product_code = 'OZF';
   
   PRODUCT_CODE APPLICATION_NAME LICSTATUS      PATCH_LEVEL
   ------------ ---------------- ---------      -----------
   OZF          Trade Management Not Installed  R12.OZF.A.4
   

Remediation

The following Metalink My Oracle Support Notes address this situation. Although the notes reference to Apps 11.5.x, the conditions and fixes are also applicable to R12, as far as I can tell.

• Note 181410.1: Quoting/Order Capture Order Feedback Queue (questions 1 and 7 have particular relevance)
• Note 740305.1: How to Purge the ASO_ORDER_FEEDBACK_T Table In Minimum time Using Asoqueue Script ?

In our case, the fix was easy, since we only had one registered consumer for the queue, and we definitely weren't using that module/application in E-Business Suite:

1. Delete the QuickCode for OZF as described in Question 1 of Note 181410.1
2. Purge and rebuild the ASO_ORDER_FEEDBACK_T table as described in Note 740305.1. The process is referenced in Note 181410.1 as well, but it's spelled out much more clearly in Note 740305.1.

Caveats

• The solution is only this easy if you're not actually using the Order Feedback queue. If you are using the queue, then truncating and rebuilding it is not such a great idea. You might still be able to reclaim some space the slow way, though.
• While neither document lists this as a requirement, you're probably better off performing this work during a maintenance window, when users aren't doing anything that might be trying to modify the queue.
• You are going to run through this process on your test system first, right? Good.