When a lot of DML operations happens on a table, the table will become fragmented because DML does not release free space from the table below the HWM. So despite having less number of rows, due to fragmentation, it consumes more space. So it is best practice to re-org the oracle table regularly.

In this below example, we will consider the table DBATEST.TEST1 :

Below script to get the current fragmentation details on the table:

OUTPUT:

Where:

unformatted_blocks : Total number of blocks unformatted
fs1_blocks : Number of blocks having at least 0 to 25% free space
fs2_blocks : Number of blocks having at least 25 to 50% free space
fs3_blocks : Number of blocks having at least 50 to 75% free space
fs4_blocks : Number of blocks having at least 75 to 100% free space
ful1_blocks : Total number of blocks full in the segment

Let’s perform some DML operation:

Check the table size:

We can see, table segment size is still same, despite deleting all the rows from the table.

Run the fragmentation script

You can see, 20825 blocks are having 75 percent to 100 percent. This is due to the fragmentation. So this table is a candidate for re-org.

TABLE REORG:

There are multiple methods of reorg , But alter table move is the easiest method to do reorg.

Check the Table size:

All the space has been released, Lets run the same fragmentation script

NOTE – If indexes are present on this table, then after re-org, rebuild this index, else they will become unusable.

ALTER INDEX INDEX_NAME REBUILD;