SQL> break on report
SQL> compute sum of data_mb on report
SQL> compute sum of indx_mb on report
SQL> compute sum of lob_mb on report
SQL> compute sum of total_mb on report
SQL> select table_name,
decode(partitioned,'/','NO',partitioned) partitioned,
num_rows,
data_mb,
indx_mb,
lob_mb,
total_mb
from (select data.table_name,
partitioning_type
|| decode (subpartitioning_type,
'none', null,
'/' || subpartitioning_type)
partitioned,
num_rows,
nvl(data_mb,0) data_mb,
nvl(indx_mb,0) indx_mb,
nvl(lob_mb,0) lob_mb,
nvl(data_mb,0) + nvl(indx_mb,0) + nvl(lob_mb,0) total_mb
from ( select table_name,
nvl(min(num_rows),0) num_rows,
round(sum(data_mb),2) data_mb
from (select table_name, num_rows, data_mb
from (select a.table_name,
a.num_rows,
b.bytes/1024/1024 as data_mb
from user_tables a, user_segments b
where a.table_name = b.segment_name))
group by table_name) data,
( select a.table_name,
round(sum(b.bytes/1024/1024),2) as indx_mb
from user_indexes a, user_segments b
where a.index_name = b.segment_name
group by a.table_name) indx,
( select a.table_name,
round(sum(b.bytes/1024/1024),2) as lob_mb
from user_lobs a, user_segments b
where a.segment_name = b.segment_name
group by a.table_name) lob,
user_part_tables part
where data.table_name = indx.table_name(+)
and data.table_name = lob.table_name(+)
and data.table_name = part.table_name(+))
order by table_name;
TABLE_NAME PARTITIONED NUM_ROWS DATA_MB INDX_MB LOB_MB TOTAL_MB
----------- ----------- --------- --------- --------- --------- ---------
T1 NO 5912285 576 1160 0 1736
T2 RANGE 1597647 75,81 70,94 0 146,75
T3 NO 700890 80 0 0 80
T4 NO 24008 4 ,5 287 291,5
T5 RANGE/HASH 60000 66,5 11,25 1,5 79,25
T6 RANGE/LIST 1572864 236,44 154,31 96,69 487,44
T7 LIST 1527191 23 95 0 118
T8 NO 19441 3 1,25 271 275,25
T9 NO 10565 2 0 0 2
--------- --------- --------- ---------
sum 1066,75 1493,25 656,19 3216,19
9 linhas selecionadas.