Home » Oracle » Oracle Database » How to find space usage of lob segments in Oracle database

How to find space usage of lob segments in Oracle database

Generally A select from the BYTES column in DBA_SEGMENTS for the table shows the table segment and its size.

select bytes/1024/1024 ,segment_name from dba_segments where segment_name='&1' and segment_type='TABLE';

But does not include LOB (CLOB or BLOB) segments sizes.To calculate the total size for the table and the associated LOBS segments a sum of the following must occur:
the bytes for the table => from dba_segments
+
the bytes for the LOB segments => from dba_lobs and dba_segments where segment_type is LOBSEGMENT
+
the bytes for the LOB Index (Lob Locator) = from dba_indexes and dba_segments

Following query can be used to find the size of the table including LOBSEGMENTS and LOBINDEX

ACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name:  '
SELECT
 (SELECT SUM(S.BYTES)                                                                                                 -- The Table Segment size
  FROM DBA_SEGMENTS S
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
 (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Segment Size
  FROM DBA_SEGMENTS S, DBA_LOBS L
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
 (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Index size
  FROM DBA_SEGMENTS S, DBA_INDEXES I
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
  "TOTAL TABLE SIZE"
FROM DUAL;

Another query to find size of lob objects oracle

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 = '&TABNAME'
and s.segment_name = l.segment_name
group by s.segment_name,s.segment_type;

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 lob segments

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

The total space allocated to the table

ACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name:  '
SELECT
 (SELECT SUM(S.BYTES)                                                                                                 -- The Table Segment size
  FROM DBA_SEGMENTS S
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
 (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Segment Size
  FROM DBA_SEGMENTS S, DBA_LOBS L
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
 (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Index size
  FROM DBA_SEGMENTS S, DBA_INDEXES I
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
  "TOTAL TABLE SIZE"
FROM DUAL;
TOTAL TABLE SIZE
---------------
1111233

Related Articles

See also  How to find the long running (longops session) in Oracle

How to move LOB segment to another tablespace

Query to check table size in Oracle

Information on FND_LOBS

How to find the High Water mark of the Oracle Table

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top