Re: Space reclamation

Lock,

As already mentioned by Shane, I think it is a better idea to focus your attention, first, on the index size. If you want to find out indexes occupying more space than they should then you can use the following script supplied by Jonathan Lewis

https://jonathanlewis.wordpress.com/index-sizing/

 I have used this script several times in real life running systems, like financial software Calypso, where rebuilding 7 or 8 indexes reduce the overall database size by 570GB.

In addition, the prevision of this script is very precise.

https://hourim.wordpress.com/2015/05/12/index-efficiency/

However, as you can read it in the comment part of Jonathan’s script, only b-tree, and function based indexes are targeted by this script

Best regards.

Mohamed Houri

Le mar. 2 mars 2021 à 13:42, Shane Borden <dmarc-noreply_at_freelists.org> a écrit :

> Focus your efforts on indexes that consume more space than its table and
> also utilize avg_row_len x num_rows compared to bytes used by the table to
> determine tables that need attention.
>
> It’s always worth maintaining your database.
>
> Shane Borden
> sborden76_at_yahoo.com
> Sent from my iPhone
>
> > On Mar 2, 2021, at 5:48 AM, 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
>
>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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