Remove Table Fragmentation

How to Remove Table Fragmentation in Oracle

by

Fragmentation leads to slowdowns in-database processing and excessive storage space wastage. This article will explain how to determine if a table has been fragmented in Oracle. Remove Table Fragmentation.

Gather Table Stats

Gather table stats which has slowness issue, if you recently gathered the table stats then skip this option.

SQL> BEGIN dbms_stats.gather_table_stats('TABLE_OWNER','TABLE_NAME',cascade=>true);

OR use the below query:

SQL>EXEC dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME', cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);

Query to generate table stats gather queries

Use the below query to generate the stats gather queries.

SQL>select 'exec dbms_stats.gather_table_stats('''||owner||''','''||table_name||''',cascade=>true);' from dba_tab_statistics where stale_stats='YES';

Check Table Size

Check table size using dba_segments view. It will help to Remove Table Fragmentation.

SQL>select sum(bytes/1024/1024/1024) from dba_segments where segment_name='TABLE_NAME';

Find fragmentation

Now check the actual table size, fragmentation size, and fragmentation percentage using the below query:

SQL>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';

Note:

Only proceed with de-fragmentation if your percentage is greater than 20%

Check table indexes

Check all indexes on the table.

SQL>select index_name from dba_indexes where table_name=TABLE_NAME;

Remove Table Fragmentation in Oracle

Using the below steps you find and remove the fragmentation from the oracle table.

Remove Table Fragmentation.

Step 1: Remove Fragmentation

You can use the below query to remove the fragmentation.

SQL>alter table table_name move;

Step 2: Rebuild the indexes

After moving the table must rebuild the indexes.

SQL>alter index index_name rebuild online;

Step 3: Gather Table States

Must gather table stats after rebuilding the indexes. As we describe all the stats gather commands in step 1, you can use any one command to gather stats.

SQL>EXEC dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME', cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);

Step 4: Check fragmented space in the table again

SQL>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';

Leave a Comment