select * from
(select t1.owner, t1.table_name,
round(t1.table_size / 1024 / 1024) + round(nvl(t3.lob_data_size,0) / 1024 / 1024) table_size_mb,
round(nvl(t2.index_size,0) / 1024 / 1024) + round(nvl(t3.lob_index_size,0) / 1024 / 1024) index_size_mb,
round(t1.table_size / 1024 / 1024) + round(nvl(t3.lob_data_size,0) / 1024 / 1024) +
round(nvl(t2.index_size,0) / 1024 / 1024) + round(nvl(t3.lob_index_size,0) / 1024 / 1024) size_mb
from (select owner, segment_name table_name, round(sum(bytes)) table_size
from dba_segments
where segment_type like 'TABLE%'
group by owner, segment_name) t1,
(select a.owner, a.table_name, sum(b.bytes) index_size
from (select owner, table_name, index_name
from dba_indexes
where index_type <> 'LOB') a,
(select owner, segment_name, bytes from dba_segments) b
where a.owner = b.owner
and a.index_name = b.segment_name
group by a.owner, a.table_name) t2,
(select a.owner, a.table_name, sum(b.lob_data_size) lob_data_size,
sum(c.lob_index_size) lob_index_size
from (select owner, table_name, segment_name, index_name
from dba_lobs) a,
(select owner, segment_name, sum(bytes) lob_data_size
from dba_segments
group by owner, segment_name) b,
(select owner, segment_name, sum(bytes) lob_index_size
from dba_segments
group by owner, segment_name) c
where a.owner = b.owner
and a.segment_name = b.segment_name
and a.owner = c.owner
and a.index_name = c.segment_name
group by a.owner, a.table_name) t3
where t1.owner = t2.owner(+)
and t1.table_name = t2.table_name(+)
and t1.owner = t3.owner(+)
and t1.table_name = t3.table_name(+)
and t1.owner not in ('SYSTEM','SYS','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS','EXFSYS','DMSYS','CTXSYS','XDB','ANONYMOUS','MDSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDDATA')
--and t1.owner = upper('SZX')
--and t1.table_name = upper('gatewaypayorderext')
order by 5 desc) x;
查询一个数据库中所有表的大小并排序
最新推荐文章于 2022-12-09 13:52:50 发布