平时我们查询表所占空间多少，一般不包含索引和大字段，为了解决一个表倒底占用多少空间呢。下面是我同事写的语句，觉的不错，所有拿出来和大家分享，大至意思是查出表大小，和索引大小，和大字段大小，和大字段索引，通过union all拼接起来，再按table_name group by分组统计

SELECT TABLE_NAME, ROUND(SUM(BYTES) / 1024 / 1024.0 / 1024, 2) VOL
FROM (SELECT IDX.TABLE_NAME, SEG.BYTES
FROM DBA_SEGMENTS SEG, DBA_INDEXES IDX
WHERE SEG.OWNER = 'ZHIFU'
AND SEG.SEGMENT_TYPE = 'INDEX'
AND SEG.SEGMENT_NAME = IDX.INDEX_NAME
AND SEG.OWNER = IDX.OWNER
UNION ALL
SELECT LOBS.TABLE_NAME, SEG.BYTES
FROM DBA_SEGMENTS SEG, DBA_LOBS LOBS
WHERE SEG.OWNER = 'ZHIFU'
AND SEG.SEGMENT_TYPE = 'LOBSEGMENT'
AND SEG.SEGMENT_NAME = LOBS.SEGMENT_NAME
AND SEG.OWNER = LOBS.OWNER
UNION ALL
SELECT LOBS.INDEX_NAME, SEG.BYTES
FROM DBA_SEGMENTS SEG, DBA_LOBS LOBS
WHERE SEG.OWNER = 'ZHIFU'
AND SEG.SEGMENT_TYPE = 'LOBINDEX'
AND SEG.SEGMENT_NAME = LOBS.INDEX_NAME
AND SEG.OWNER = LOBS.OWNER
UNION ALL
SELECT SEG.SEGMENT_NAME, SEG.BYTES
FROM DBA_SEGMENTS SEG
WHERE SEG.OWNER = 'ZHIFU'
AND SEG.SEGMENT_TYPE = 'TABLE') SPC
GROUP BY SPC.TABLE_NAME
ORDER BY ROUND(SUM(BYTES) / 1024 / 1024.0 / 1024, 2) DESC

ITPub博客

查询表占用空间准确记录

最新文章