Hello experts, I have a problem where I depend on your expertise.
I have a query that returns all user tables and their sizes.
My aim is to distribute all tables into 6 buckets with a maximum size of 500G each.
Can you please give me an example of how to achieve that? I am not yet that familiar with analytical functions.
Did some stuff with 10g but since then no longer. And 12c, which I am using nowadays might offer more convenient ways.
However, to be honest, I do not even have an idea how to create those buckets with an older Oracle release.
Maybe you can give me some advice.
select owner, segment_name, gr_mb,
round(RATIO_TO_REPORT(gr_mb) over (partition by 1) * 100,2) perc_total_size,
sum(gr_mb) over (partition by 1) total_size
from (
select owner, segment_name, round(bytes/1024/1024/1024) gr_mb
from dba_segments
where owner like 'SCH%' and segment_type = 'TABLE' and segment_name not like 'BIN$%'
order by bytes desc
)