Sometimes a quick sum of the used bytes in DBA_SEGMENTS is not enough to find the total size of a table.
If you have Large Object type columns defined in the table, the total table size can potentially be far greater than what you'd think.
Let's look at an example.
I have two tables in two differen schemas, with the same name. The tables are identical, and they both contain a LOB column:
SQL> desc livedocs.documents Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(19) VERSION NUMBER(19) DOC_ID NOT NULL NUMBER(19) TYPE NOT NULL VARCHAR2(255 CHAR) NUM_PAGES NUMBER(15) CREATED TIMESTAMP(6) LAST_CHANGED TIMESTAMP(6) CONTENTS NOT NULL BLOB
Check the tables' sizes:
SELECT OWNER,SEGMENT_NAME,SUM(BYTES)/1024/1024/1024 "table size (GB)"
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('DOCUMENTS')
AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS')
GROUP BY OWNER,SEGMENT_NAME;
Result:
| OWNER | SEGMENT_NAME | table size (GB) |
|---|---|---|
| LIVEDOCS | DOCUMENTS | 2,5 |
| ARCHIVEDDOCS | DOCUMENTS | 1,875 |
Let's find the name of the LOB segments and their corresponding indexes:
(The LOB indexes are internal data structures only. See this post.)
SELECT OWNER,TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,INDEX_NAME
FROM DBA_LOBS
WHERE TABLE_NAME='DOCUMENTS'
AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS');
| OWNER | TABLE_NAME | SEGMENT_NAME | COLUMN_NAME | INDEX_NAME |
|---|---|---|---|---|
| ARCHIVEDDOCS | DOCUMENTS | ARCHIVED_DOCS_LOB | CONTENT | SYS_IL0000123181C00008$$ |
| LIVEDOCS | DOCUMENTS | LIVE_DOCS_LOB | CONTENT | SYS_IL0000194213C00009$$ |
How much space have been allocated to these LOB segments?
SELECT OWNER,SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024/1024) "LOB size (GB)"
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN
(
SELECT SEGMENT_NAME
FROM DBA_LOBS WHERE TABLE_NAME='DOCUMENTS'
AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS')
)
GROUP BY OWNER,SEGMENT_NAME
;
| OWNER | SEGMENT_NAME | LOB size (GB) |
|---|---|---|
| LIVEDOCS | LIVE_DOCS_LOB | 1256 |
| ARCHIVEDDOCS | ARCHIVED_DOCS_LOB | 3642 |
Putting it together, you can list both the table and its dependent LOB segments like this:
SELECT S.OWNER "Owner",NVL(S.SEGMENT_NAME, 'TABLE TOTAL SIZE') "Segment name",ROUND(SUM(S.BYTES)/1024/1024/1024,1) "Segment size (GB)"
FROM DBA_SEGMENTS S
WHERE S.SEGMENT_NAME IN ('DOCUMENTS')
AND S.OWNER IN ('LIVEDOCS','ARCHIVEDOCS')
OR S.SEGMENT_NAME IN (
(
SELECT L.SEGMENT_NAME FROM DBA_LOBS L
WHERE L.TABLE_NAME = 'DOCUMENTS'
AND L.OWNER = S.OWNER
AND L.OWNER in ('LIVEDOCS','ARCHIVEDOCS')
)
)
GROUP BY S.OWNER,ROLLUP(S.SEGMENT_NAME)
ORDER BY 1,2,3;
The resulting listing is:
| Owner | Segment name | Segment size (GB) |
|---|---|---|
| ARCHIVEDDOCS | ARCHIVED_DOCS_LOB | 3642,4 |
| ARCHIVEDDOCS | DOCUMENTS | 1,9 |
| ARCHIVEDDOCS | TABLE TOTAL SIZE | 3644,3 |
| LIVEDOCS | DOCUMENTS | 2,5 |
| LIVEDOCS | LIVE_DOCS_LOB | 1255,9 |
| LIVEDOCS | TABLE TOTAL SIZE | 1258,4 |
Addendum, 2025-02-06:
With assistance from Chat GPT, I realized that a better query would be to use named subqueries, or CTEs (Common Table Expressions). They can be built using the WITH-clause in SQL, and referred to in the main query:
set lines 200
col OWNER format a30
col TABLE_NAME format a30
col TABLE_SIZE_GB format 99
col LOB_SEGMENT_NAME format a30
col LOB_COLUMN_NAME format a30
col LOB_DATA_TYPE format a30
col LOB_SIZE_GB format 999
BREAK ON OWNER
WITH
-- CTE to get the tables with LOB objects
lob_tables AS (
SELECT DISTINCT TABLE_NAME, OWNER
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'SCOTT'
AND DATA_TYPE IN ('NCLOB', 'BLOB', 'RAW' )
AND TABLE_NAME NOT LIKE 'BIN$%'
),
-- CTE to get LOB segment names with their corresponding table and column names
lob_segments AS (
SELECT DISTINCT L.OWNER,
L.TABLE_NAME,
L.SEGMENT_NAME AS LOB_SEGMENT_NAME,
C.COLUMN_NAME AS LOB_COLUMN_NAME,
C.DATA_TYPE AS LOB_DATA_TYPE
FROM DBA_LOBS L
JOIN DBA_TAB_COLUMNS C
ON L.TABLE_NAME = C.TABLE_NAME
AND L.COLUMN_NAME = C.COLUMN_NAME
WHERE L.OWNER = 'SCOTT'
),
-- CTE to get the table size in GB
table_sizes AS (
SELECT S.OWNER,
S.SEGMENT_NAME AS TABLE_NAME,
ROUND(SUM(S.BYTES)/1024/1024/1024, 1) AS TABLE_SIZE_GB
FROM DBA_SEGMENTS S
WHERE S.OWNER = 'SCOTT'
AND EXISTS (SELECT 1 FROM lob_tables LT WHERE LT.TABLE_NAME = S.SEGMENT_NAME)
GROUP BY S.OWNER, S.SEGMENT_NAME
),
-- CTE to get the LOB size in GB
lob_sizes AS (
SELECT L.OWNER,
L.TABLE_NAME,
L.SEGMENT_NAME AS LOB_SEGMENT_NAME,
ROUND(SUM(S.BYTES)/1024/1024/1024, 1) AS LOB_SIZE_GB
FROM DBA_LOBS L
JOIN DBA_SEGMENTS S
ON L.SEGMENT_NAME = S.SEGMENT_NAME
AND L.OWNER = S.OWNER
WHERE L.OWNER = 'SCOTT'
GROUP BY L.OWNER, L.TABLE_NAME, L.SEGMENT_NAME
)
-- Final query to combine everything
SELECT TS.OWNER,
TS.TABLE_NAME,
TS.TABLE_SIZE_GB,
LS.LOB_SEGMENT_NAME,
LS.LOB_COLUMN_NAME,
LS.LOB_DATA_TYPE,
LZ.LOB_SIZE_GB
FROM table_sizes TS
JOIN lob_segments LS
ON TS.OWNER = LS.OWNER
AND TS.TABLE_NAME = LS.TABLE_NAME
JOIN lob_sizes LZ
ON LS.OWNER = LZ.OWNER
AND LS.LOB_SEGMENT_NAME = LZ.LOB_SEGMENT_NAME
ORDER BY TS.OWNER, TS.TABLE_NAME, LS.LOB_SEGMENT_NAME;
This gives you a practical row-by-row view of everything you need to know about the LOB segments:
| OWNER | TABLE_NAME | TABLE_SIZE_GB | LOB_SEGMENT_NAME | LOB_COLUMN_NAME | LOB_DATA_TYPE | LOB_SIZE_GB |
|---|---|---|---|---|---|---|
| SCOTT | TABLE1 | 0 |
SYS_LOB0003064134C00015$$ | MYCOL1 | NCLOB | 0 |
| TABLE1 | 0 |
SYS_LOB0003064134C00028$$ | MYCOL2 | NCLOB | 0 |
|
| TABLE2 | 0,2 |
SYS_LOB0003064148C00005$$ | MYCOL1 | NCLOB | 1 |
|
| TABLE2 | 0,2 |
SYS_LOB0003064148C00017$$ | MYCOL2BLOB | BLOB | 0 |
|
| TABLE3 | 0,2 |
SYS_LOB0003064159C00004$$ | MYCOL1 | NCLOB | 237 |
|
| TABLE3 | 0,1 |
SYS_LOB0003064143C00003$$ | MYCOL1 | NCLOB | 0 |
