查询一个数据库中所有表的大小并排序

来源：互联网发布：mac使用ps怎么合并图层编辑：程序博客网时间：2024/05/16 19:38
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;
0 0