oracle中查询所有表存储空间的sql语句

select segment_name,sum(bytes)/(1024*1024)||'M' from user_segments
where LOWER(segment_name) in('table_name1','table_name2')
group by segment_name

user_segments是系统表，保存了表的分段信息

完善sql

表大小<1M的显示0M

使用case when

select segment_name,
       case when sum(bytes)/(1024*1024)<1 then 0
       else sum(bytes)/(1024*1024)
       end
from user_segments
where LOWER(segment_name) in('table_name1','table_name2')
group by segment_name

按in中表出现的顺序显示查询结果

即给每行记录编号排序

select segment_name,
       case when sum(bytes)/(1024*1024)<1 then 0
       else sum(bytes)/(1024*1024)
       end
from user_segments
where LOWER(segment_name) in('table_name1','table_name2')
group by segment_name
order by DECODE(LOWER(segment_name),'table_name1','1','table_name2','2')
-- 如果发现排序不对的话应该是按asc2码排字符序了，需转成数字排序：
-- ... 
-- order by to_number( decode(...) )