Information on FND_LOBS

What is stored?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).

How to purge entries from FND_LOBS?

The concurrent program “Purge Obsolete Generic File Manager Data” is used to purge FND_LOBS,
and it will purge entries according to the type:
– entries for the Application Help (iHelp) – will not be purged
– attachements – will be purged if expired
– exports – will be purged

The expiration of attachments should be done via application, and not manually updating the table.

You can see entries which have an expiration date by the program_name running:

select program_name,count(*)
from FND_LOBS
where expiration_date is not NULL
group by program_name;

Entries with no expiration date could be found running:

select program_name,count(*)
from FND_LOBS
where expiration_date is NULL
group by program_name;

How to find the space allocated in the lobsegments versus used space?

To find how much space is actually used by the lobsegments you can run:

select sum(dbms_lob.getlength (FILE_DATA)) from FND_LOBS;
SUM(DBMS_LOB.GETLENGTH(FILE_DATA))
———————————-
57253782456

The total space allocated in the extents could be found running:

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
———- ——————————— ——————
525472547345 SYS_LOB0000039757C00004$$ LOBSEGMENT

How to find the space used by each program

select
program_name,round(sum(dbms_lob.getlength (FILE_DATA))/1024/1024,0) “Size(M)”
from APPS.fnd_LOBS
where expiration_date is NULL
group by program_name order by 2 desc

PROGRAM_NAME Size(M)
——————————– ———-
FNDATTCH 864
FND_HELP 280
export 7
HRMS_ADI 5
PERWSIMG 3
IBE 0
PER_P11D_gb_UK.pdf 0
.

How to change the PCTVERSION

ALTER TABLE APPLSYS.FND_LOBS MODIFY LOB (FILE_DATA) ( PCTVERSION 0 );