Thanks Much. So it seems the tabe shrink keeping all the
parameters default(like pct free 10 etc) won't give us much benefit as that
will eventually grow to the same size after some days because of the nature
of the data pattern. But i was afraid if any negative effect would be there
if going outside the given defaults, but as you suggested and i am also
seeing we have many of the tables which are insert only type of transaction
tables and are read heavily but very few Update/Delete happens on
them(mostly in case of data fix etc). So is it just fine to rebuild both
table and index partition with pctfree 1 and there won't be any negative
impacts in future?
But from the reply it seems like , the index size reduction will have
permanent impact on reducing the size and giving space back to storage. And
also again this should also make the index scan faster because of the
compact size post rebuild. Please correct me if I am wrong.
Regards
Lok
On Tue, Mar 2, 2021 at 9:11 PM Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
You can check PCTFREE as well if you want to save space,maybe you don't
need more than 1
On Tue, Mar 2, 2021, 15:53 Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:
Personally, I would use DBMS_SPACE.FREE_BLOCKS procedure. As for
recovering space in the tables, it's "alter table shrink space". That,
however, will not buy you much. Tables will grow back to their previous
sizes rather quickly. Buying better storage will likely help you improve
performance.
On 3/2/21 5:48 AM, Lok P 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
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--
//www.freelists.org/webpage/oracle-l
