Re: Space reclamation

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_at_gmail.com> 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_at_gmail.com> 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
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

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