Oracle-DB: Taking fragmentation into account when calculating the free tablespace
Even if you think to have enough free space in tablespace your operation may end up in: ORA-01653 unable to extend table <xy> by <z> in tablespace <TS> The reason for this often unexpected behavior is: The table needs a contiguous free space in tablespace in next extent size to allocate a new extent. If free space in total is enough available but only in smaller slices than the required size of the extent to allocate than this will happen. Several years / Oracle versions ago fragmentation issues have been addressed by uniform extent size for tablespaces. Today this issue ist mostly addressed by locally managed tablespaces with an automatically defined limited number of used extent sizes. Locally managed tablespaces reduce the risk of fragmentation issues, but not completely. So especially if less space remains available in tablespace fragmentation sometimes becomes important. One way to check a database for the risk of running into ORA-01653 is to look fo...