由于分区表中的数据量非常大，怎么用SQL查询准确的统计出分区所占用数据库空间大小？

分区可包含其自身，其自身也有子分区，可以用以下SQL进行统计： select t1.table_name,t2.bytes,t2.owner,t2.tablespace_name from dba_tab_partitions t1 left join dba_segments t2 on t1.table_owner = t2.owner and t1.tablespace_name = t2.tablespace_name and t1.partition_name

= t2.partition_name and t1.table_name = t2.segment_name where t1.table_owner = 'TEST' and t1.tablespace_name = 'TEST' union all -- 返回子分区占用空间大小 select t3.table_name,t2.bytes,t2.owner,t2.tablespace_name from dba_tab_subpartitions t1 left join dba_tab_partitions t3

on t1.table_owner = t3.table_owner and t1.partition_name = t3.partition_name and t1.tablespace_name

= t3.tablespace_name and t1.table_name = t3.table_name left join dba_segments t2 on t1.subpartition_name = t2.partition_name and t1.table_name = t2.segment_name -- and

t1.table_owner = t2.owner and t1.tablespace_name = t2.tablespace_name
where t1.table_owner = 'TEST' and t1.tablespace_name = 'TEST' )t4 group by t4.segment_name,t4.owner,t4.tablespace_name )t5, dba_data_files t6 where t6.tablespace_name = 'TEST' group by t5.table_name,t5.bytes,t5.owner,t5.tablespace_name )t7 where t7.table_name not like '%$%' group by table_name,bytes,total_bytes,owner,tablespace_name; select owner,table_name,tablespace_name,bytes,(round((round(sum(bytes)/(1024*1024),2))/(round

(total_bytes/(1024*1024),2)),4)100) " used(%)" ,round(sum(bytes)/(10241024),2) from ( select t5.table_name,t5.bytes as bytes,t5.owner,t5.tablespace_name,sum(t6.bytes) as total_bytes

from ( select t4.segment_name as table_name,sum(t4.bytes) as bytes,t4.owner,t4.tablespace_name from ( -- 返回表数据 占用空间量 select t1.segment_name,t1.bytes,t1.owner,t1.tablespace_name from dba_segments t1,dba_tables t2 where t1.tablespace_name = t2.tablespace_name and t1.owner = t2.owner and t1.segment_name = t2.table_name and t1.tablespace_name = 'TEST' and t1.owner = 'TEST'

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments group by segment_name;