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 |
