查询表占用空间准确记录

    平时我们查询表所占空间多少，一般不包含索引和大字段，为了解决一个表倒底占用多少空间呢。下面是我同事写的语句，觉的不错，所有拿出来和大家分享，大至意思是查出表大小，和索引大小，和大字段大小，和大字段索引，通过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博客 ” ，链接：http://blog.itpub.net/27252036/viewspace-1433111/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27252036/viewspace-1433111/