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