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@xxxxxxxxxxxxx> 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@xxxxxxxxx
Sent from my iPhone
On Mar 2, 2021, at 5:48 AM, Lok P <loknath.73@xxxxxxxxx> wrote:version 11.2.0.4, 12.1.0.2.0 and 19C. We got an ask from management
Hello Listers, We have got three different Oracle databases with rdbms
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
the total block to find the eligible segments and then rebuild those?
1) Should we go by checking the empty_blocks column and compare it with
the effort because those spaces will eventually be consumed again by data
2)I read a few blog posts which suggest reclaiming space may not worth
insert/manipulation. So is that true and we should not take up this effort
in the first place?
Regards
Lok
--
//www.freelists.org/webpage/oracle-l
