Identifying the FND_LOBS segments attached to the database

Total number of attachments per program
—————————————–
select distinct program_name, count(*) from fnd_lobs group by program_name order by 2 desc;

Total number of program with Expiration Date
———————————————
select program_name,count(*) from FND_LOBS where expiration_date is not NULL group by program_name order by 2 desc;

Total number of program with no Expiration Date
————————————————

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

Find Size Programwise
———————-
select program_name, count(0), round(sum((dbms_lob.getlength(FILE_DATA)-1)/1024/1024/1024),1) size_gb
from fnd_lobs group by program_name order by size_gb desc;
Size of the Attachements Applicationwise – FNDATTCH
—————————————————–
select fa.application_name, count(0), round(sum(dbms_lob.getlength (FILE_DATA)/1024/1024/1024),1) size_gb
from fnd_lobs fl,
 fnd_document_entities fde,
 fnd_attached_documents fad,
 fnd_documents_vl fd,
 fnd_application_vl fa
where fd.media_id = fl.file_id
 and fd.document_id = fad.document_id
 and fl.program_name = ‘FNDATTCH’
 and fad.entity_name = fde.data_object_code
 and fa.application_id = fde.application_id
group by fa.application_name
order by size_gb desc;
Size of the Attachements Applicationwise – NULL
—————————————————–
select fa.application_name, count(0), round(sum(dbms_lob.getlength (FILE_DATA)/1024/1024/1024),1) size_gb
from fnd_lobs fl,
 fnd_document_entities fde,
 fnd_attached_documents fad,
 fnd_documents_vl fd,
 fnd_application_vl fa
where fd.media_id = fl.file_id
 and fd.document_id = fad.document_id
 and fl.program_name is NULL
 and fad.entity_name = fde.data_object_code
 and fa.application_id = fde.application_id
group by fa.application_name
order by size_gb desc;
Size as per the Expiration Date
——————————–
select program_name,round(sum(dbms_lob.getlength (FILE_DATA))/1024/1024/1024,0) “Size(G)”
from APPS.fnd_LOBS where expiration_date is NULL group by program_name order by 2 desc;
PCTVERSION of the segment
————————–
select SEGMENT_NAME, PCTVERSION from dba_lobs where TABLE_NAME =’FND_LOBS’;
  • January 22, 2019 | 20 views
  • Comments