Oracle 查询表大小（含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 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;