Re: Space reclamation

If you want some background and fairly thorough discussion on the way to think about space management and the options for reclaiming "wasted" space (which is often described very vaguely as "fragmentation"), then you could start with this mini-series:
https://jonathanlewis.wordpress.com/2010/07/13/fragmentation-1/

There is only one important point about any case where someone says "this object is much larger than it needs to be to hold the data it's supposed to hold"
If they're right there's a reason why that happened, and you need to find out what that reason is, and when you know the reason you can decide on the approach you can take to minimise the excess.

Regards
Jonathan Lewis

On Tue, 2 Mar 2021 at 10:48, Lok P <loknath.73_at_gmail.com> wrote:

> Hello Listers, We have got three different Oracle databases with rdbms
> version 11.2.0.4, 12.1.0.2.0 and 19C. We got an ask from management
> suggesting to look into all the objects(Tables , Indexes) having >25% free
> space and get those reclaimed to have some storage back to disk. The
> intention is two fold 1)To have unused space back to the storage/disk(which
> in turns saves cost) 2) It may help us in improving the table/index scan
> because of compact size. And the team wants us to have those reclaim jobs
> set/run on a regular basis so that it can automatically find and run
> the reorg on its own . I have two questions here
>
> 1) Should we go by checking the empty_blocks column and compare it with
> the total block to find the eligible segments and then rebuild those?
>
> 2)I read a few blog posts which suggest reclaiming space may not worth the
> effort because those spaces will eventually be consumed again by data
> insert/manipulation. So is that true and we should not take up this effort
> in the first place?
>
> Regards
> Lok
>

--
http://www.freelists.org/webpage/oracle-l