MOS Doc. ID: 298698.1, "Avoiding abnormal growth of FND_LOBS table in Applications 11i" tells us, "Of all the tables that occupy a very large amount of space within the APPLSYSD and APPLSYSX tablespaces of Oracle Applications instances, FND_LOBS is usually one of the top 10. This is because it stores all the attachments that have been uploaded to Oracle Applications. There is a LOB field within this table called FILE_DATA, and the corresponding LOB segment (e.g., APPLSYS.SYS_IL0000680397C00004$$) is where the actual attachment data is stored, and it is usually very large. It is important that its size be controlled."
MOS Doc. ID: 829235.1, "FAQ - Performance considerations for FND_LOBS" describes LOBS further: "FND_LOBS stores information about all LOBs managed by the Generic File Manager (GFM). Each row includes the file identifier, name, content-type, and actual data. Each row also includes the dates the file was uploaded and when it will expire, the associated program name and tag, and the language and Oracle characterset. The file data, which is a binary LOB, is stored exactly as it is uploaded from a client browser, which means that no translation work is required during a download to make it HTTP compliant. Therefore uploads from non-browser sources will have to prepare the contents appropriately (for instance, separating lines with CRLF). The program_name and program_tag may be used by clients of the GFM for any purpose, such as striping, partitioning, or purging the table if the program is de-installed. They are otherwise strictly informative.
Some of the data that gets into this table belongs to old/expired exports. For every request for an export, an entry in the FND_LOBS table is recorded. This data must be purged regularly. There is a purge program available to purge this data called "Purge Obsolete Generic File Manager Data" (FNDGFMPR). FNDGFMPR is a PL/SQL procedure that deletes old obsolete uploaded files (loaded to the database) for the programs FND_HELP, export and FND_ATTACH, these are programs that are run under the FNDGFU (Generic File Manager Access Utility). MOS Doc. ID: 216541.1 describes how to add The Concurrent Program "Purge Obsolete Generic File Manager Data" To The Sysadmin User."
MOS Doc. ID: 1165208.1, "Questions on Purge Obsolete Generic File Manager Data" says that the tables affected by the Purge Obsolete Generic File Manager Data Concurrent Program are FND_LOBS and FND_LOB_ACCESS. And don't think that I'm the only one fussing about the size of that table: there are at least two enhancements requests asking for ways to more effectively purge the FND_LOBS table. For the PO module, for example, there are two enhancement requests for deleting attachments:
- Bug 3899857 MASS DELETE PROGRAM FOR DELETING ATTACHMENT - Purchasing does not set an expiration date on the attachments. Also, there is no concurrent request available currently that deletes Purchasing attachments in bulk. Enhancement Request Bug 3899857 is already logged with Oracle Development requesting this functionality.
- Bug 5676144 NEED A WAY TO CONTROL THE GROWTH OF PO ATTACHMENTS IN THE FND_LOBS TABLE - requests a functionality to allow purging PDF attachments created by the PO Output for Communication for approved purchase orders. Usually the rapid increase in table space is caused by large PDF files, generated because of using large sized files for the company logos (header or footer). As a workaround, one can consider using smaller sized files for the logos.
- Concurrent Program: OEXPURGS - Order Purge Selection
- Concurrent Program: OEXPURGE - Order Purge
We ran the queries included in the assorted MOS Documents and the "Purge Obsolete Generic File Manager Data" Concurrent Program against a client's database to see what we could conclude about their LOBS:
The Concurrent Program "Purge Obsolete Generic File Manager Data" will not purge entries for the Application Help (iHelp) and will only purge attachments or exports if they are expired. Oracle notes that the expiration of attachments should be done via an application, and not manually updating the table, so don't try to clean up the FND_LOBS table by using TOAD.
You can see entries that have an expiration date by program_name with the following query:
where expiration_date is not NULL
group by program_name;
PROGRAM_NAME COUNT(*)-------------- ----------
export 7694 - related to Export, these will be purged
Recommendation: Schedule the “Purge Obsolete Generic File Manager Data” to run periodically. Then re-run the query above to make sure it has successfully removed the expired data.
Our client ran the purge and it deleted all of the exports and freed up about 125M of space within the FND_LOBS table.
DATA THAT WILL NOT BE PURGED
Entries with no expiration date, which means they will not be purged, can be found by running this query:
where expiration_date is NULL
group by program_name;
PROGRAM_NAME COUNT(*)---------------------- ----------
FNDATTCH 14948 - related to Attachments
FND_HELP 59198 - related to iHelp
Oracle E Records 2
17 rows selected.
Oracle says that it is common to see rows with pdf and rtf files.
To find how much space is actually used by the lobsegments:
select sum(dbms_lob.getlength (FILE_DATA)) from FND_LOBS;
To find the total space allocated in the extents:
select sum(bytes), s.segment_name, s.segment_type
from dba_lobs l, dba_segments s
where s.segment_type = 'LOBSEGMENT'
and l.table_name = 'FND_LOBS'
and s.segment_name = l.segment_name
group by s.segment_name,s.segment_type;
SUM(BYTES) SEGMENT_NAME SEGMENT_TYPE---------- ------------------------- ------------------
6,040,576,000 SYS_LOB0000040605C00004$$ LOBSEGMENT
Thus, about a gigabyte of this client’s space is allocated but not used.
To find the space used by each program that will not be purged because the expiration_date field is null:
program_name,round(sum(dbms_lob.getlength (FILE_DATA))/1024/1024,0) "Size(M)"
where expiration_date is NULL
group by program_name order by 2 desc;
PROGRAM_NAME Size(M)-------------------------------- ----------
Oracle E Records 0
17 rows selected.
So about 4650M will not be purged.
MOS Doc. ID: 298698.1, "Avoiding abnormal growth of FND_LOBS table in Applications 11i" explains that "to maintain read consistency, Oracle creates new LOB page versions every time a lob changes. PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data. The PCTVERSION can be set to the percentage of LOB's that are occasionally updated.
The FND_LOBS table's FILE_DATA LOB column usually gets the data uploaded only once, but it is read multiple times. Hence, it is not necessary to keep older versions of LOB data. It is recommended that this value be changed to "0". By default PCTVERSION is set to 10%. It must be set to 0% explicitly. The value can be changed any time in a running system."
You can use following SQL to find the current PCTVERSION value :
select SEGMENT_NAME, PCTVERSIONfrom dba_lobs
where TABLE_NAME ='FND_LOBS';
SEGMENT_NAME PCTVERSION------------------------- ----------
Recommendation: Set PCTVERSION to 0 using the command: alter table applsys.fnd_lobs modify lob (file_data) (pctversion 0);
To help manage the size of the FND_LOBS table, Oracle’s main recommendation is to set PCTVERSION to 0 and use a reasonably sized next_extent. We checked the LOBS tables for our client, and they had very few extents, so the tables are probably sized adequately.
Oracle also notes that "if using locally managed tablespaces, then the number of extents is not as much of a problem as it was in the past with dictionary managed tablespaces. Since the LOB segments are usually very large, they are treated differently from other columns. While other columns can be guaranteed to give consistent reads, these columns are not. This is because it is difficult to manage LOB data rollback segments due to their size, unlike other columns. So they do not use rollback segments. Usually only one copy exists, so the queries reading that column may not get consistent reads while other queries modify them. In these cases, the other queries will get "ORA-22924 snapshot too old" errors."
To see the approximate size of the extent:
selectmin(round(dbms_lob.getlength (FILE_DATA)/1024,0)) "Min Size(K)",
round(avg(round(dbms_lob.getlength (FILE_DATA)/1024,0)),0) "Avg Size(K)",
max(round(dbms_lob.getlength (FILE_DATA)/1024,0)) "Max Size(K)"
Min Size(K) Avg Size(K) Max Size(K)----------- ----------- -----------
0 59 1173
Oracle says that there is no "magic" number for the extent size and a compromised setting should be used, observing the following:
- Extent should not be too small, to avoid constant extent allocation (which would cause HW enqueues in tables with LOBS)
- Extents should not be too big, to avoid wasted space
- Extents size should be bigger than the majority of the size of one lob segments
The following script from MOS Doc. ID: 963222.1. “Orphaned records in FND_LOBS table when uploading attachments using FNDATTACH form” can be used to check if you have any orphaned records in the FND_LOBS table that have been inserted through the FNDATTACH form.
FROM FND_LOBS FL
WHERE NOT EXISTS
FROM FND_DOCUMENTS_TL FDT, FND_DOCUMENTS FD, FND_ATTACHED_DOCUMENTS FAD
WHERE FD.DOCUMENT_ID = FDT.DOCUMENT_ID
AND FAD.DOCUMENT_ID = FD.DOCUMENT_ID
AND FDT.MEDIA_ID = FL.FILE_ID
AND FD.DATATYPE_ID = 6)
AND PROGRAM_NAME = 'FNDATTCH'
AND EXPIRATION_DATE IS NULL;
These 447 orphans are caused by a code bug in file (FNDATTCH.pld) which allows the attachment to be loaded into the FND_LOBS table even if the user does not confirm that the upload completed successfully. To implement the solution, execute the following steps:
1. To prevent creation of orphaned records in FND_LOBS going forward, download and review the readme and pre-requisites for:
Release 11i Customers:
Patch 9004099 - ORPHANED RECORDS IN FND_LOBS TABLE CONSUME DATABASE DISK SPACE
Release 12 Customers:
Patch 9454616 - PROGRAM_TAG,UPLOAD_DATE,EXPIRATION_DATE,PROGRAM_NAME ARE NULL
2. Ensure that you have taken a backup of your system before applying the recommended patch.3. Apply the patch in a test environment.
4. Retest the issue.
5. Migrate the solution as appropriate to other environments.
6. In order to get a data fix to delete orphaned records in the FND_LOBS table that have been inserted using the FNDATTACH form , log a Service Request with Oracle Support.
Recommendation: Apply Patch 9004099 and then log an SR with Oracle Support to get help with cleaning up the orphaned records.
Once you have addressed the records with expired data by running the “Purge Obsolete Generic File Management Data” Concurrent Program, and changed the PCTVERSION to 0, and adjusted the NEXT_EXTENT value to stop the overly aggressive growth of the allocated space, then it will be worthwhile to see if there is a way to reclaim unused space. Read the documentation very carefully and test very thoroughly, though, as these notes describe making substantial changes to the database.
If there is a lot of DML activity in the FND_LOB table, some space may be reclaimed by moving the table to another tablespace and then moving back to the original tablespace. Refer to:
MOS Note: 303709.1 “Reclaiming unused space in APPLSYSD tablespace”MOS Note: 130814.1 "How to move LOB Data to Another Tablespace"
For additional information, refer to:
MOS Note: 118531.1 How to Compute the Size of a Table containing Outline CLOBs and BLOBsMOS Note: 386341.1 How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM
LIMITING ATTACHMENT SIZE
It is also possible to limit the size of attachments. The profile option 'Upload File Size Limit' can be used to limit the size of uploaded attachments.
Recommendation: Research file size limits by reviewing the following MOS Notes:
MOS Note: 605377.1 How To Set A Maximum File Size For Attachments?MOS Note: 604458.1 How to Limit The Attachment File Size?