检查表大小

select segment_name,sum(bytes)/1024/1024 MB from user_segments group by segment_name order by sum(bytes)/1024/1024 desc; 

我通过 user_segments 统计的大小为：22 MB 

我使用dba_lobs 统计的大小为：306 GB

select segment_name,t.segment_type,bytes/1024/1024/1024 GB,blocks from dba_segments t where bytes>1024*1024*1024 and segment_type!='INDEX' order by bytes/1024/1024/1024 desc

去掉索引的

用这个SQL，可以把表和表的LOB一起统计出来

select distinct ab.OWNER,ab.TABLE_NAME,
ab.SEGMENT_NAME,
ab.TABLESPACE_NAME, ac.PARTITION_NAME,
sum(ac.BYTES / 1024 / 1024 / 1024) GB
from dba_lobs ab, DBA_SEGMENTS ac
where ab.OWNER IN (SELECT USERNAME
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.default_tablespace NOT IN
('SYSTEM','SYSAUX'))
and ab.SEGMENT_NAME = ac.segment_name
and ab.OWNER = ac.owner
--and ab.TABLESPACE_NAME = 'LOB_DATA'
group by ab.TABLE_NAME, ab.SEGMENT_NAME, ab.TABLESPACE_NAME, ab.OWNER,ac.PARTITION_NAME
order by 6 desc;

我是这样统计的：

SELECT *
FROM (SELECT l.owner,
l.table_name,
l.column_name,
l.segment_name,
l.tablespace_name,
ROUND(s.bytes / 1024 / 1024 / 1024, 2) size_gb
FROM dba_lobs l
JOIN dba_segments s
ON s.owner = l.owner
AND s.segment_name = l.segment_name
ORDER BY 6 DESC)
WHERE OWNER = 'USERNAME';

你是问一个表如果有包含lob，如何统计吗？
SELECT
(SELECT SUM(S.BYTES) – The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER(’&SCHEMA’) AND
(S.SEGMENT_NAME = UPPER(’&TABNAME’))) + (SELECT SUM(S.BYTES) 　　　　　　　　　　　 – The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = UPPER(’&SCHEMA’) AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER(’&TABNAME’) AND L.OWNER = UPPER(’&SCHEMA’))) + (SELECT SUM(S.BYTES) – The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER(’&SCHEMA’) AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER(’&TABNAME’) AND INDEX_TYPE = ‘LOB’ AND I.OWNER = UPPER(’&SCHEMA’)))
“TOTAL TABLE SIZE”
FROM DUAL;