How to find how much space is actually used by the lobsegments



Last updated on November 28th, 2015 at 12:35 pm

Let us take the example of FND_LOBS tables in Oracle Apps Database.FND_LOBS has LOB datatype column FILE_DATA

How to  find how much space is actually used by the lobsegments

Query to find the actual bytes used by the lob datatype
select sum(dbms_lob.getlength (FILE_DATA)) from FND_LOBS;
SUM(DBMS_LOB.GETLENGTH(FILE_DATA))
———————————-
13313

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


Leave a Reply