# Oracle 计算带blob表的大小

通用方法：

SELECT
(SELECT ROUND(SUM(S.BYTES)/1024/1024,2)     -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('&SCHEMA')
AND  S.SEGMENT_NAME = UPPER('&TABNAME')) +
(SELECT ROUND(SUM(S.BYTES)/1024/1024,2)   -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = L.OWNER
AND L.SEGMENT_NAME = S.SEGMENT_NAME
AND L.TABLE_NAME = UPPER('&TABNAME')
AND L.OWNER = UPPER('&SCHEMA')) +
(SELECT ROUND(SUM(S.BYTES)/1024/1024,2)    -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = I.OWNER
AND  I.INDEX_NAME = S.SEGMENT_NAME
AND I.TABLE_NAME = UPPER('&TABNAME')
AND INDEX_TYPE = 'LOB'
AND I.OWNER = UPPER('&SCHEMA')) +
(SELECT ROUND(SUM(S.BYTES)/1024/1024,2)    -- The Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = I.OWNER
AND  I.INDEX_NAME = S.SEGMENT_NAME
AND I.TABLE_NAME = UPPER('&TABNAME')
AND INDEX_TYPE = 'NORMAL'
AND I.OWNER = UPPER('&SCHEMA'))
AS "TOTAL TABLE SIZE"
FROM DUAL;  
测试：
ZXY@ zxy>SELECT
2   (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)     -- The Table Segment size
3    FROM DBA_SEGMENTS S
4    WHERE S.OWNER = UPPER('zxy')
5    AND  S.SEGMENT_NAME = UPPER('t_sfzslxx_1100')) +
6    (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)   -- The Lob Segment Size
7    FROM DBA_SEGMENTS S, DBA_LOBS L
8    WHERE S.OWNER = L.OWNER
9    AND L.SEGMENT_NAME = S.SEGMENT_NAME
10    AND L.TABLE_NAME = UPPER('t_sfzslxx_1100')
11    AND L.OWNER = UPPER('zxy')) +
(SELECT ROUND(SUM(S.BYTES)/1024/1024,2)    -- The Lob Index size
13    FROM DBA_SEGMENTS S, DBA_INDEXES I
14    WHERE S.OWNER = I.OWNER
15    AND  I.INDEX_NAME = S.SEGMENT_NAME
16    AND I.TABLE_NAME = UPPER('t_sfzslxx_1100')
17    AND INDEX_TYPE = 'LOB'
18    AND I.OWNER = UPPER('zxy')) +
19    (SELECT ROUND(SUM(S.BYTES)/1024/1024,2)    -- The Index size
20    FROM DBA_SEGMENTS S, DBA_INDEXES I
21    WHERE S.OWNER = I.OWNER
22    AND  I.INDEX_NAME = S.SEGMENT_NAME
23    AND I.TABLE_NAME = UPPER('t_sfzslxx_1100')
24    AND INDEX_TYPE = 'NORMAL'
25    AND I.OWNER = UPPER('zxy'))
26     AS "TOTAL TABLE SIZE"
27  FROM DUAL;

TOTAL TABLE SIZE
----------------
26.37

• 广告
• 抄袭
• 版权
• 政治
• 色情
• 无意义
• 其他

120