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’;
Recent Posts
