Re: Space reclamation

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