oracle查看段占用空间

1、看用户表数据段的大小
select segment_name, sum(bytes)/1024/1024 Mb from user_segments where segment_type='TABLE' group by segment_name;

 

2、看索引段的大小

select segment_name ,sum(bytes)/1024/1024 Mb from user_segments where segment_type ='INDEX' group by segment_name;

3、看分区表的大小
select segment_name,sum(bytes)/1024/1024 Mb from user_segments where segment_type='TABLE PARTITION' group by segment_name;

 

4、比较各中段的大小

with 
X as 
(

select sum(Mb) as table_total_MB from
        (select segment_name, sum(bytes)/1024/1024 Mb from user_segments where segment_type='TABLE' group by segment_name)
        
)
, Y as 
(

select sum(Mb) as index_total_MB from
        (select segment_name, sum(bytes)/1024/1024 Mb from user_segments where segment_type='INDEX' group by segment_name)
        
)
, Z as 
(

select sum(Mb) as table_partition_total_MB from
        (select segment_name, sum(bytes)/1024/1024 Mb from user_segments where segment_type='TABLE PARTITION' group by segment_name)
        
)
select table_total_MB, index_total_MB ,table_partition_total_MB from X,Y,Z