简介：
参考文档：
Avoiding abnormal growth of FND_LOBS table in Applications 11i [ID 298698.
参考文档：
FND_LOBS table's FILE_DATA LOB column usually gets the data uploaded only once, but 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%. So, most of the instances usually have it set to 10%, it must be set to 0% explicitly. The value can be changed any time in a running system.
Use the following query to find out currently set value for PCTVERSION:
select PCTVERSION from dba_lobs where TABLE_NAME = 'FND_LOBS' and COLUMN_NAME='FILE_DATA';
PCTVERSION
----------
10
PCTVERSION can be changed using the following SQL (it can be run anytime in a running system):
ALTER TABLE FND_LOBS MODIFY LOB (FILE_DATA) ( PCTVERSION 0 );
Please be aware :
The solution shows, how to avoid the increase of the table in future - it does not shrink the current tablespace !
The only supported way to shrink the tablespace is to export and re-import the whole Database.
xfan-tiger1$> oerr ora 22924 22924, 00000, “snapshot too old” // *Cause: The version of the LOB value needed for the consistent read was // already overwritten by another writer. // *Action: Use a larger version pool. xfan-tiger1$> oerr ora 01555 01555, 00000, “snapshot too old: rollback segment number %s with name \”%s\” too small” // *Cause: rollback records needed by a reader for consistent read are // overwritten by other writers // *Action: If in Automatic Undo Management mode, increase undo_retention // setting. Otherwise, use larger rollback segments
There is only one standard method to purge the table, that is concurrent program "Purge Obsolete Generic File Manager Data". This program will not purge FND_HELP and other records without EXPIRATION_DATE. It will purge the data with program_name=export and the data with an expiration date. You can use following SQL statement to query the data which has no expiration date select program_name,count(*) from FND_LOBS where expiration_date is NULL group by program_name;
An example output would look like: PROGRAM_NAME COUNT(*) -------------------------------- ---------- FND_HELP 46439 export 9 FNDATTCH 6
FND_HELP is the data of iHelp and should not be deleted otherwise you will not use the help function. export is the data generated by "export" from form, this kind of data can be purged freely. FNDATTCH is attachment. There are also other kinds of "program_name" from the result, even "null" program_name, these files are from different modules and products, you may need to check with end users if they can be purged. Please keep in mind, the FND_LOBS table is used by different Oracle Applications features (such as Export, Attachments, FND Help, etc) and different application product modules utilize FND_LOBS to store a variety of data, both temporary data and active on-going business data. There is no any program or utility to mass purge business records because we don't know which file is useful and which is not.
In summary, to purge the data of FND_LOBS, you need to set the data to have an EXPIRATION_DATE and then run the concurrent program. We can only suggest to keep "FND_HELP" untouched and for other files, you need to check with application users. If you are not sure what the file is, you can follow Note 1457782.1 to download the file and then check with end-users. Other methods to delete/rebuild the table are not supported.
Thanks a lot for your explaination and I got to know it.
I have checked that all the attachments that uploaded by users have its colum in FND_LOBS with the value of NULL for both EXPIRATION_DATE and UPLOAD_DATE. so to purge these part of attachments(confirmed these attachments to be useless in the test environmnet), what I do is like following: 1. Set a real date before for the colum of EXPIRATION_DATE for all that part of attachments( EXPIRATION_DATE is null and UPLOAD_DATE is null). 2. run the request "Purge Obsolete Generic File Manager Data" to purge the data . 3. I guess the size of SYS_LOB0000048967C00004$$ would be reduced after that.
Step 1 and 2 are correct method to purge the table FND_LOBS. Regarding the question 3, the size of the segment does not change. You need to perform additional steps like re-allocate to reduce the size of a segment. You can refer to Note 303709.1 for details. If you need more assistance regarding the sizing issue, please open a new SR with product "Oracle Applications Technology Stack"-->"Database".
Assign the "Purge Obsolete Generic File Manager Data" Concurrent
Program to the Request Group "System Administrator Reports".
1. Access Oracle Applications as the Sysadmin user.
2. Select the System Administrator responsibility.
3. Navigate to the following menus:
Security / Responsibility / Request
4. Place the cursor in the "Group" field of the
"Request Groups" form.
5. Select "View" from the menu.
Select "Find" from the menu.
Select "System Administrator Reports".
6. Place the cursor in the "Type" column.
Use "File" from the menu to create a "New" record.
7. Set the "Type" field to "Program".
8. Select "Purge Obsolete Generic File Manager Data" for the "Name" field.
9. Select "File" from the menu.
Select "Save and Proceed" from the menu.
Program Parameters:
Expired: Enter "Y" if you want to purge expired data only. Enter "N" if you want the purge to include all data. The default is "Y."
Program Name: Enter the program name(s) to process. Leave blank to process all programs.
Program Tag: Enter the program tag(s) to process. Leave blank to process all program tags. Tags are identifiers used by the Generic File Manager for categorization purposes.
alter table <owner>.<table_name> move lob (<lob_column_name>)
store as <segment_name>;
这里我们需要运行:
alter table applsys.FND_LOBS move lob (FILE_DATA)
store as SYS_LOB0000048967C00004$$
（通过select * from dba_lobs where table_name = 'FND_LOBS'来确定上面的选项，<lob_column_name>就是COLUMN_NAME列的值，<segment_name>就是SEGMENT_NAME列的值，owner和table_name就是表名和所有者了）
