oracle 查询有lob的表,查询Oracle数据库中带有lob字段的某一个表的大小

注意：由于lob字段有独立的lob segment来存储，故对于带有lob字段的表，不能仅仅查询dba_segments.

以下脚本来自：

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

经过修改:改为了NVL(SUM(S.BYTES),0)

SQL> col "TOTAL TABLE SIZE" format 99999999999999---注意：以下sql可以直接执行。为了方便复制该脚本，因此不贴SQL语句的log，而是放上了执行时的SQL语句。SELECT

(SELECT NVL(SUM(S.BYTES),0)

-- The Table Segment size

FROM DBA_SEGMENTS S

WHERE S.OWNER = UPPER('

LC0019999'

) AND

(S.SEGMENT_NAME = UPPER('

ZWPZK'

))) +

(SELECT NVL(SUM(S.BYTES),0)

-- The Lob Segment Size

FROM DBA_SEGMENTS S, DBA_LOBS L

WHERE S.OWNER = UPPER('

LC0019999'

) AND

(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('

ZWPZK'

) AND L.OWNER = UPPER('

LC0019999'

))) +

(SELECT NVL(SUM(S.BYTES),0)

-- The Lob Index size

FROM DBA_SEGMENTS S, DBA_INDEXES I

WHERE S.OWNER = UPPER('

LC0019999'

) AND

(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('

ZWPZK'

) AND INDEX_TYPE = '

LOB'

AND I.OWNER = UPPER('

LC0019999'

)))

"TOTAL TABLE SIZE"FROM DUAL;

TOTAL TABLE SIZE---------------- 3571869286SQL> select 3571869286/1024/1024/1024 from dual;

3571869286/1024/1024/1024-------------------------- 3.3265625