Query the size of a table with a lob field in the Oracle database

Query the size of a table with a lob field in the Oracle database

Note: Because the lob field has an independent lob segment for storage, for a table with a lob field, you cannot only query dba_segments.

The following scripts are from:

How to Compute the Size of a Table containing Outline CLOBs and BLOBs [Article ID 118531.1]

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

Modified to NVL (SUM (S. BYTES), 0)

SQL & gt; col & quot; TOTAL TABLE SIZE & quot; format 99999999999999

--- Note: The following SQL statements can be executed directly. For the convenience of copying the script, the SQL statement at execution is placed instead of the log of the SQL statement.

1. SELECT
2. (Select nvl (SUM (S. BYTES), 0) -- The Table Segment size
3. FROM DBA_SEGMENTS S
4. Where s. OWNER = UPPER ('lc00%9') AND
5. (S. SEGMENT_NAME = UPPER ('zwpzk') +
6. (Select nvl (SUM (S. BYTES), 0) -- The Lob Segment Size
7. FROM DBA_SEGMENTS S, DBA_LOBS L
8. Where s. OWNER = UPPER ('lc00%9') AND
9. (L. SEGMENT_NAME = S. SEGMENT_NAME and l. TABLE_NAME = UPPER ('zwpzk') and l. OWNER = UPPER ('lc001129 ') +
10. (Select nvl (SUM (S. BYTES), 0) -- The Lob Index size
11. FROM DBA_SEGMENTS S, DBA_INDEXES I
12. Where s. OWNER = UPPER ('lc00%9') AND
13. (I. INDEX_NAME = S. SEGMENT_NAME and I. TABLE_NAME = UPPER ('zwpzk') AND INDEX_TYPE = 'lob' and I. OWNER = UPPER ('lc009709 ')))
14. "Total table size"
15. From dual;
17. TOTAL TABLE SIZE
18. ----------------
19. 3571869286
20. SQL> select 3571869286/1024/1024/1024 from dual;
21. 35718692864/1024/1024/1024
22. --------------------------
23. 3.3265625