oracle查看表的大小（包含lob字段和索引）

1. 查看所有表的大小（不包含系统表）

select owner, table_name, sum(size_m)
  from (select s.owner,
               s.segment_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tables t
         where s.owner = t.owner
           and s.segment_name = t.table_name
        union
        select s.owner,
               l.table_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_lobs l
         where s.owner = l.owner
           and s.segment_name = l.segment_name
        union
        select s.owner,
               i.table_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_indexes i
         where s.owner = i.owner
           and s.segment_name = i.index_name
        union
        select s.owner,
               p.table_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tab_partitions p
         where s.owner = p.table_owner
           and s.segment_name = p.partition_name)  
       where  owner not in ('SCOTT',
'ORACLE_OCM',
'OJVMSYS',
'SYSKM',
'XS$NULL',
'GSMCATUSER',
'MDDATA',
'SYSBACKUP',
'DIP',
'SYSDG',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'GSMUSER',
'AUDSYS',
'FLOWS_FILES',
'DVF',
'MDSYS',
'ORDSYS',
'DBSNMP',
'WMSYS',
'APEX_040200',
'APPQOSSYS',
'GSMADMIN_INTERNAL',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'XDB',
'ORDPLUGINS',
'DVSYS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'LBACSYS',
'OUTLN',
'SYSTEM',
'SYS')
 group by owner, table_name
 order by sum(size_m) desc;

2.查看某个表的大小

select owner, table_name, sum(size_m)
  from (select s.owner,
               s.segment_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tables t
         where s.owner = t.owner
           and s.segment_name = t.table_name
        union
        select s.owner,
               l.table_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_lobs l
         where s.owner = l.owner
           and s.segment_name = l.segment_name
        union
        select s.owner,
               i.table_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_indexes i
         where s.owner = i.owner
           and s.segment_name = i.index_name
        union
        select s.owner,
               p.table_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tab_partitions p
         where s.owner = p.table_owner
           and s.segment_name = p.partition_name)  
       where  owner  in ('&owner') AND TABLE_NAME='&table_name'
 group by owner, table_name
 order by sum(size_m) desc;

或

 select owner, table_name, sum(size_m)
  from (select s.owner,
               s.segment_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tables t
         where s.owner = t.owner
           and s.segment_name = t.table_name
        union
        select s.owner,
               l.table_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_lobs l
         where s.owner = l.owner
           and s.segment_name = l.segment_name
        union
        select s.owner,
               i.table_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_indexes i
         where s.owner = i.owner
           and s.segment_name = i.index_name
        union
        select s.owner,
               p.table_name as table_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tab_partitions p
         where s.owner = p.table_owner
           and s.segment_name = p.partition_name)  
       where  owner='test'  AND TABLE_NAME='test_table'
 group by owner, table_name
 order by sum(size_m) desc

3.查询某个表空间下的所有表的大小

查询某个表空间下的所有表的大小
select owner, table_name, sum(size_m)
  from (select s.owner,
               s.segment_name as table_name,
               s.tablespace_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tables t
         where s.owner = t.owner
           and s.segment_name = t.table_name
           and s.tablespace_name='test_tbs'
        union
        select s.owner,
               l.table_name as table_name,
               s.tablespace_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_lobs l
         where s.owner = l.owner
           and s.segment_name = l.segment_name
           and s.tablespace_name='test_tbs'
        union
        select s.owner,
               i.table_name as table_name,
               s.tablespace_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_indexes i
         where s.owner = i.owner
           and s.segment_name = i.index_name
           and s.tablespace_name='test_tbs'
        union
        select s.owner,
               p.table_name as table_name,
               s.tablespace_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tab_partitions p
         where s.owner = p.table_owner
           and s.segment_name = p.partition_name
            and s.tablespace_name='test_tbs')  
       where  owner not in ('SCOTT',
'ORACLE_OCM',
'OJVMSYS',
'SYSKM',
'XS$NULL',
'GSMCATUSER',
'MDDATA',
'SYSBACKUP',
'DIP',
'SYSDG',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'GSMUSER',
'AUDSYS',
'FLOWS_FILES',
'DVF',
'MDSYS',
'ORDSYS',
'DBSNMP',
'WMSYS',
'APEX_040200',
'APPQOSSYS',
'GSMADMIN_INTERNAL',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'XDB',
'ORDPLUGINS',
'DVSYS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'LBACSYS',
'OUTLN',
'SYSTEM',
'SYS')
 group by owner, table_name
 order by sum(size_m) desc

select owner, table_name, sum(size_m)
  from (select s.owner,
               s.segment_name as table_name,
			   s.tablespace_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tables t
         where s.owner = t.owner
           and s.segment_name = t.table_name
        union
        select s.owner,
               l.table_name as table_name,
			   s.tablespace_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_lobs l
         where s.owner = l.owner
           and s.segment_name = l.segment_name
        union
        select s.owner,
               i.table_name as table_name,
			   s.tablespace_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_indexes i
         where s.owner = i.owner
           and s.segment_name = i.index_name		  
        union
        select s.owner,
               p.table_name as table_name,
			   s.tablespace_name,
               (s.bytes / 1024 / 1024) as size_m
          from dba_segments s, dba_tab_partitions p
         where s.owner = p.table_owner
           and s.segment_name = p.partition_name)  
       where  owner not in ('SCOTT',
'ORACLE_OCM',
'OJVMSYS',
'SYSKM',
'XS$NULL',
'GSMCATUSER',
'MDDATA',
'SYSBACKUP',
'DIP',
'SYSDG',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'GSMUSER',
'AUDSYS',
'FLOWS_FILES',
'DVF',
'MDSYS',
'ORDSYS',
'DBSNMP',
'WMSYS',
'APEX_040200',
'APPQOSSYS',
'GSMADMIN_INTERNAL',
'ORDDATA',
'CTXSYS',
'ANONYMOUS',
'XDB',
'ORDPLUGINS',
'DVSYS',
'SI_INFORMTN_SCHEMA',
'OLAPSYS',
'LBACSYS',
'OUTLN',
'SYSTEM',
'SYS')
 group by owner, table_name
 order by sum(size_m) desc