Last updated on June 28th, 2019 at 04:16 am
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
How to move LOB segment to another tablespace
Query to check table size in Oracle
How to find the High Water mark of the Oracle Table