DB version: 11.2.0.4
OS : RHEL 6.7
Is it safe to assume that the below query will accurately calculate the size of a partitioned table ?
select sum(bytes/1024/1024) sizeMB from user_segments where segment_name = '<NAME OF THE PARTITIONED TABLE>';
-- The basic test shown below looks fine.
create table t1
(
col1 number,
created_date date
)
PARTITION BY RANGE(created_date)
(
PARTITION sales_jan2017 VALUES LESS THAN(TO_DATE('01/FEB/2017','DD/MON/YYYY')),
PARTITION sales_feb2017 VALUES LESS THAN(TO_DATE('01/MAR/2017','DD/MON/YYYY')),
PARTITION sales_mar2017 VALUES LESS THAN(TO_DATE('01/APR/2017','DD/MON/YYYY')),
PARTITION sales_apr2017 VALUES LESS THAN(TO_DATE('01/MAY/2017','DD/MON/YYYY')),
PARTITION sales_may2017 VALUES LESS THAN(TO_DATE('01/JUN/2017','DD/MON/YYYY'))
);
populating the partitions with data
insert into t1
select
rownum,
to_date ('01-JAN-2017','DD-MON-YYYY') + dbms_random.value(0,145) created_date
from dual
connect by rownum <= 1000000 ;
commit;
SQL> col segment_name format a15
SQL> col partition_name format a20
SQL>
SQL> select segment_name, partition_name,bytes/power(1024,2) sizeMB from user_segments where segment_name = 'T1';
SEGMENT_NAME PARTITION_NAME SIZEMB
--------------- -------------------- ----------
T1 SALES_JAN2017 8
T1 SALES_MAR2017 8.5
T1 SALES_MAY2017 8
T1 SALES_FEB2017 8
T1 SALES_APR2017 8
SQL> select sum(bytes/power(1024,2)) sizeMB from user_segments where segment_name = 'T1';
SIZEMB
----------
40.5