DB version: 11.2.0.4
OS : Oracle Linux 6.5
In my shop, we want to do some data cleanup (purging) so that we don't have to buy additional storage.
As far as I know, only when you can do a manual resize of the datafile, you can release space back to filesystem/Diskgroup.
Both SHRINK and DELETE won't release space back to the datafile but it will create empty blocks for future INSERTs/UPDATEs.
Imagine a tablespace with only one datafile (32GB) and only 1 segment. This segment can be created anywhere in a datafile. In this 32GB datafile oracle might place this segment in the 25th GB. So even if the total space consumed in this datafile is 10mb , you can only resize to 25GB ! The workaround for this is to move the segment and then run a ALTER DATABASE DATAFILE.... RESIZE command.
But, this 'segment moving' approach is not practical when want to deal with lots of datafiles with lots of segments in it.
Coming back to my original question on SHRINK and DELETE ; if SHRINK and DELETE cannot free up space in a datafile , there is practically no difference between SHRINK and DELETE as far as space saving is concerned. Right ?
So the only advantage of SHRINK over delete ,I can think of is that SHRINK will lower the HWM at the segment level so that Oracle won't have to scan all the blocks (lot of empty blocks sometimes) under the HWM of the segment.
Are my assumptions correct ?