Very often DBAs are requested( by user or by force to optimze space requirement or performance) to re-org tables and indexes to get free space after a mass housekeeping of application or after some weeks of application run. In order to take back space from datafile/tablespace DBA can do one or many of few standard things:
1. move table to same or different tablespace. Rebuild of indexes is must after move of table as rowid of rows are changed.
2. do export and import of table. Need business outage.
3. rebuild only indexes(with/without online) to claim free space of indexes only.
4. alter table table_name shrink space cascade; This will try to put blocks togather starting from start of first free block in the datafile. Cascade option will take care of indexes, so no need to rebuild indexes in this case.
5. online re-org of tablespace/tables with exception for tables with data type long and raw.
The Oracle extents are mapped to OS data blocks. The mapping can be revealed by dba_free_space and dba_extents. We can check the actual layout of segments and free space in datafile by below command:
select file_id, block_id first_block, block_id+blocks-1 last_block,
segment_name
from dba_extents
where tablespace_name = ‘BHUSHAN_DATA’
union all
select file_id, block_id, block_id+blocks-1, ‘free’
from dba_free_space
where tablespace_name = ‘BHUSHAN_DATA’
order by file_id, first_block
/
We can play around with above query to get information of each segment, related datafile etc.
There are 2 types of Fragmentation in Oracle:
1.Honeycomb fragmentation:
This is the fragmentation when free space is side by side. Honeycomb fragmentation donot bother you much until it is at the very beginning of the datafile. Mostly Shrink space cascade for the segment which is live before honeycomb fragmentation gets you rid of this fragmentation.
2.swiss cheese fragmentation:
This is the fragmentation when the free extents are separated by live segments.
If we query any tablespace with above shared script, we may get something like :
20 73400 73407 BHUSHAN00
20 73408 73415 free
20 73416 73423 BHUSHAN00
Mostly, we have combination of both fragmentation and thats make it challenging.
Often DBA fire any of the re-org command without analyzing the order in which we should do so as to take benefit to the max and get the re-org done completely.
In my scenario, I had 15 indexes on a datafile in a tablespace. The total size on segments was 500mb and total size of datafile was 15000mb. Still I was not able to claim any space to OS for other datafile. There was lot of fragmentation for sure which got revealed on running above mentioned query.
After my effort I could claim all 11000mb.
The best approach i found was:
find the order in which extents for segment is scattered in datafile. Start rebiuld index/shrink table from the bottom or top where you find more free blocks.
Oracle has several tools to help reclaim disk space:
-
alter database datafile xxx.dbf resize yym; – This will remove space that the physical “end” if the datafile, and the command will not work if any segments extend beyond your resize boundary.
-
alter tablespace xxx coalesce – This command will reclaim space from honeycomb fragmentation
Oracle leaves the high-water mark alone after rows are deleted, and you can reclaim space at the table level with these techniques, all of which lower the high water mark for the table, thereby freeing-up the space:
-
export-import – For a complete reorganization and space reclamation, export/import allows you to restructure your files and reclaim lost space.
-
dbms_redefinition – This procedure will reorganize a table while it remains online for updates.
-
alter table xxx shrink – If you were 10g and beyond you could use “alter table xxx shrink space compact;” syntax.
-
You can look at the coalesce table syntax. Unlike the “deallocate unused space” syntax which removes space above the high-water mark, “coalesce” puts together discontiguous fragmented extents. There are two type of space fragmentation in Oracle.
First is the honeycomb fragmentation, when the free extents are side by side, and the “Swiss Cheese” fragmentation, when the extents are separated by live segments.
alter table xxx coalesce;
You can also deallocate unused space. Oracle notes that the “deallocate unused space” clause is used to to explicitly deallocate unused space at “the end” of a segment and makes that space available for other segments within the tablespace.
alter table xxx deallocate unused space;
alter index xxx deallocate unused space;
Internally, Oracle deallocates unused space beginning from the end of the objects (allocated space) and moving downwards toward the beginning of the object, continuing down until it reaches the high water mark (HWM). For indexes, “deallocate unused space” coalesces all leaf blocks within same branch of b-tree, and quickly frees up index leaf blocks for use.
postgres 
