Kevin Kempf's Blog

January 19, 2011

Who is consuming my APPS_TS_MEDIA tablespace in FND_LOBS ?

Filed under: 11i — kkempf @ 10:51 am

Numbers don’t lie

Every quarter, I have a job which logs tablespace usage in my ERP.  It’s useful to look at growth trends and rates, and it has recently come to my attention that the fastest growing tablespace in my Production environment is APPS_TS_MEDIA.   In the past year, it’s grown 11gb.  This number may not seem like much, unless you consider a few things.  First, it’s growing as fast as my TX_INDEX and TX_DATA tablespaces.  Second, multiply 10gb by the number of instances you have.  In my case, with dataguard, that 10gb becomes 90gb.  Now add the disk overhead for storing 30 days worth of backups of that extra 10gb.   In short order, it becomes a concern.

Changes

Looking back at the past year, what has gone live to spark this 9000% growth in the tablespace?  iProcurement.  It turns out, everyone is attaching every .pdf quote and .doc to their purchase requistion.  Combine this with the continued pace of docs, xls and pdfs for the likes of product definition and order entry, and the growth is mostly accounted for.

Queries to glean insight into fnd_lobs

fnd_lobs isn’t an easy table to garner information from, but I was finally able to build a profile of what was in there using this query:

select   
  fd.document_id    
 ,pp.full_name  
 ,to_char (fd.creation_date,'DD-MON-YYYY') file_date  
 ,fl.file_name  
 ,round(dbms_lob.getlength(fl.file_data)/1024/1024,1) Size_Mb    
 ,fdt.description
from   
  fnd_documents fd  
 ,fnd_documents_tl fdt  
 ,fnd_lobs fl  
 ,fnd_user fu  
 ,per_people_x pp
where   
  fd.document_id = fdt.document_id
and   
  fdt.media_id = fl.file_id(+)
and   
  fd.created_by = fu.user_id
and   
  fu.employee_id = pp.person_id(+)
and    
  round(dbms_lob.getlength(fl.file_data)/1024/1024,1) > 0 
order by   
  fd.creation_date desc
;

Where does this all take me?

Knowing what’s in fnd_lobs doesn’t exactly help me purge it.  Oracle has a few relevant hits such as a concurrent request called Purge Obsolete Generic File Manager Data.  Unfortunately, this request doesn’t do a thing for me.  More to follow on this subject.

Advertisements

2 Comments

  1. Hi Kevin,

    Even we are having similiar problem .
    I checked MOS and found 2 enhancement requests , but there is no progress on that . Were you able to find a solution ?

    Comment by AppsDBA — March 20, 2011 @ 9:16 pm

    • The short answer is no. Oracle has no inclination nor seeming motivation to work on this issue. There is an enhancement request or 2, as you noted, revolving around purging before date XX or whatnot, but they are unavailable. I’m afraid as far as 11i goes, this will never see daylight as it’s just too old.

      Comment by kkempf — March 21, 2011 @ 5:33 am


RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Blog at WordPress.com.