What is Table Fragmentation?
Fragmentation is caused in table when we do update, delete operation within the table which make the space available in between the table which lead to fragmentation.
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 the data is deleted from the table, the free space needs to be visible which can be reclaimed or reused, but Oracle acquires new blocks rather than using the freed blocks, thus physically taking much more space than actually consumed and thus causing a hole in the table.
Check top 10 fragmented table in Schema
select * from (
select owner,table_name,round((blocks*8),2) “size (kb)” ,
round((num_rows*avg_row_len/1024),2) “actual_data (kb)”,
(round((blocks*8),2) – round((num_rows*avg_row_len/1024),2)) “wasted_space (kb)”, ((round((blocks * 8), 2) – round((num_rows * avg_row_len / 1024), 2)) /
round((blocks * 8), 2)) * 100 – 10 “reclaimable space % “
from dba_tables
where owner in (‘&SCHEMA_NAME’ ) and (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 5 desc ) where rownum < 10;
How to remove Fragmentation in a table ?
There are different ways of removing Fragmentation -> Export/Import, Table recreation or moving tables to a different or same tablespace. Here we will discuss on one of the methods :
1. Analyze the table and if required gather statistics
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‘, method_opt=> ‘for all indexed columns size skewonly’, granularity => ‘ALL’, degree => 8 ,cascade => true,estimate_percent => 15);
2. Check the actual size, Fragmented size and percentage of Fragmentation in the table
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’;
Sample Output :
TABLE_NAME AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE percentage
—————— ————————————— ——————- —————————— —————-
INFRAXPERTZZ 487 934430.41MB 452945.09MB 481485.32MB 51.5271458
If you see the fragmented percentage is more, then proceed with the further steps
3. Check whether there are any indexes created in the table. If yes, please make a note of the index, since you need to rebuild the index again
select index_name from dba_indexes where table_name=’&TABLE_NAME’;
4. Move tables to the same tablespace to remove the fragmentation:
alter table <owner>.<table_name> move;
5. Rebuild the index
alter index owner.index_name rebuild online;
6. Gather Statistics
EXEC dbms_stats.gather_table_stats(ownname => ‘TABLE_OWNER‘, tabname => ‘TABLE_NAME‘, method_opt=> ‘for all indexed columns size skewonly’, granularity => ‘ALL’, degree => 8 ,cascade => true,estimate_percent => 15);