FND_LOBS is one of the big and important table in Oracle Apps . Lets see various important things about this table
What is stored in FND_LOBS?
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_LOB0000057C00004$$ 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 );
How to see contents of the LOB?
-- Read from fnd_lobs a given file_id
set serveroutput on size 1000000;
declare
my_lob BLOB;
Buffer RAW(255);
Amount BINARY_INTEGER := 255;
Position INTEGER := 1;
begin
select file_data into my_lob
from fnd_lobs
where file_id = &enter_file_id;
dbms_lob.open(my_lob, dbms_lob.lob_readonly);
DBMS_OUTPUT.PUT_LINE('Start of data');
loop
DBMS_LOB.READ(my_lob, Amount, Position, Buffer);
/* Process the buffer: */
DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer));
Position := Position + Amount;
end loop;
dbms_lob.close(my_lob);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('End of data');
end;
/
How To Download Attachment File From fnd_lobs
- Get the FILEID from the database for the LOB file you want to download, for example:
select FILE_ID,FILE_NAME from FND_LOBS where FILE_ID=123599;
FILE_ID FILE_NAME
---------- -------------
1212 TEST.XLS
2. Execute FNDGFU command. For file example above:
FNDGFU apps/apps 0 Y DOWNLOAD=1212 TEST.XLS
Related Articles
how to move lob segment : how to move lob segment from one tablespace to another, how to reclaim space after major deleted in lob segment, how to use shrink command in LOB segments
Attachments in Oracle Applications: Check out this post on Attachments in Oracle Applications R12, how they are stored, tables involved in the process and queries
Oracle EBS Auditing : How to setup Oracle EBS Auditing, how to add tables in audit group, how to select column for auditing, audit trail update concurrent request
Create EBS user from backend : This post is about how to Create EBS user from backend, how to assign application responsibility to the user created