Table Fragmentation and re-organisation in Oracle Database

How to Find and Remove Table Fragmentation?
Let us discuss.

What is Table Fragmentation?
In simple language you can say, table has less data but size is too high which is not expected at all.

Brief explanation:

When rows are inserted into the table, the high watermark(used blocks) of the table moved forward in order to accommodate new rows into the table. But during delete operation, oracle doesn’t allow high watermark to shift backward in order to decrease table size and release the free space.
Ideally once we delete the data from the table, the free space should be released or reused but additionally oracle acquire new blocks to accommodate the new rows for insertion which causes hole into the table.

How to remove table fragmentation in oracle?

There are different ways to remove fragmentation like table export/import, moving tables to same or different tablespace and table recreation. But here we will discuss most easy and common ways of removing fragmentation.

Steps To Check and Remove Fragmentation:

#1 :  Gather table statistics

In order to find the exact difference between the total size and actual size of the table from dba_segments and dba_tables views. You can also check the last analysed date of a table. If table has recently analysed and stale_stats is no then you can skip this step.

select table_name,last_analyzed,stale_stats from user_tab_statistics where table_name='&TABLE_NAME';

exec dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,granularity =>'AUTO', method_opt=> 'for all indexed columns size skewonly', degree => 8, cascade => TRUE );


#2 : Check table size

select sum(bytes)/1024/1024/1024 "size_GB" from dba_segments where segment_name='&TABLE_NAME' and owner='&OWNER';


#3 :  Check actual table size, fragmented size and percentage of fragmentation in a table
(Use 8 in place of 16 if your block size is 8k)

SELECT table_name,
  avg_row_len,
  ROUND(((blocks*16/1024)),2)  ||'MB' "TOTAL_SIZE",
  ROUND((num_rows*avg_row_len/1024/1024),2)  ||'MB' "ACTUAL_SIZE",
  ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)  ||'MB' "FRAGMENTED_SPACE",
  (ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/ROUND(((blocks*16/1024)),2))*100 "percentage"
FROM all_tables
WHERE table_name='&TABLE_NAME' and owner='&OWNER';

e.g.,

TABLE_NAME                    |AVG_ROW_LEN|TOTAL_SIZE|ACTUAL_SIZ|FRAGMENTED|percentage
------------------------------|-----------|----------|----------|----------|----------
SALES_ORDERS                  |       3754|444.64MB  |54.67MB   |389.97MB  |87.7046599


 If you find more than 20% fragmentation then you can proceed for de-fragmentation. You can also de-fragment based on size you are going to reclaim from the above mentioned  query.


#4 : Check the indexes on the table

select index_name from dba_indexes where table_name='&TABLE_NAME' and owner='&OWNER';


#5 : Remove fragmentation by moving tables to same tablespace. You can also move tables to different tablespace.

alter table <table_name> move;

For moving tables into different tablespace

alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace>;


#6 : Rebuild the indexes.

alter index index_name rebuild online;

#7 : Gather the statistics:

SQL> exec dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,granularity =>'AUTO', method_opt=> 'for all indexed columns size skewonly', degree => 8, cascade => TRUE );


#8: Finally check the table size and percentage of fragmentation:
(Use 8 in place of 16 if your block size is 8k)

SELECT table_name,
  avg_row_len,
  ROUND(((blocks*16/1024)),2) ||'MB' "TOTAL_SIZE",
  ROUND((num_rows*avg_row_len/1024/1024),2) ||'MB' "ACTUAL_SIZE",
  ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
  (ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/ROUND(((blocks*16/1024)),2))*100 "percentage"
FROM all_tables
WHERE table_name='&TABLE_NAME' and owner='&OWNER';

Note: If table move is not helping to avoid fragmentation, then proceed to drop and re-create table with downtime. Make sure you should take care of all FK constraints.