What is Table Fragmentation in an Oracle And How To Remove Table Fragmentation?

by

Table fragmentation means when you updated or deleted data in a table and you wanted to reclaim space, yes you could, but Oracle immediately can’t release or free up the space in a table. But this can leave holes in a table called table fragmentation.

Cause: Database slowness and unnecessary filled up storage space.

Here are many ways to identify and fix table fragmentation in Oracle:

  1. Check table size
  2. Gather table stats
  3. Remove fragmentation
  4. Rebuild indexes on table

How do I find table fragmentation in Oracle?

The below SQL query will show you the total table size with fragmentation and percent (%) of size to reclaim. Please provide table_name and schema_name as input.

set pages 50000
lines 32767
select owner,table_name,round((blocks8),2)||’kb’ “Fragmented size”, round((num_rowsavg_row_len/1024/1024),2)||’mb’ “Actual size”,
round((blocks8),2)-round((num_rowsavg_row_len/1024/1024),2)||’mb’,
((round((blocks8),2)-round((num_rowsavg_row_len/1024),2))/round((blocks8),2))100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’ /

How to remove table fragmentation in an Oracle?

There are different ways to remove table fragmentation.

  1. Export/Import
  2. Table recreation
  3. Move table to same or a different tablespace

Gather statistics to Analyze the table.

SQL> select table_name,last_analyzed,stale_stats from user_tab_statistics where table_name=’&TABLE_NAME’;

EXEC dbms_stats.gather_table_stats(ownname => ‘PRODDB’, tabname => ‘MYTABLE’, method_opt=> ‘for all indexed columns size skewonly’, granularity => ‘ALL’, degree => 8 ,cascade => true, estimate_percent => 15);

Check the actual size, Fragmented size and percentage of Fragmentation in the table.

col TABLE_NAME for a10
col TOTAL_SIZE for a10
col ACTUAL_SIZE for a11
col FRAGMENTED_SPACE for a16
select table_name,avg_row_len,round(((blocks16/1024)),2)||’MB’ “TOTAL_SIZE”, round((num_rowsavg_row_len/1024/1024),2)||’MB’ “ACTUAL_SIZE”,
round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2) ||’MB’ “FRAGMENTED_SPACE”,
(round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2)/round(((blocks16/1024)),2))100 “percentage” from all_tables WHERE table_name=’&TABLE_NAME’;

Please check if any indexes created in the table. after that you need to rebuild the index again.

SQL> select index_name from dba_indexes where table_name=’&TABLE_NAME’;

To find table_name and tablespace_name:

SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from all_tables where TABLE_NAME = ‘Mytable’;

Move table to same or different tablespace to remove the fragmentation:

SQL> alter table myowner.mytable move;

Note: Please provide your owner name and table name in the above sql query.

Rebuild the index if any:

SQL> alter index owner.index_name rebuild online;

Again 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);

To find top 10 fragmented table in Schema:

set lines 170
set pages 10000
col owner format a30
col table_name format a30
col TOTAL_SIZE format 99999999999
col ACTUAL_SIZE format 999999999999
col FRAGMENTED_SPACE format 999999999999
select * from (
select owner,table_name,round((blocks8),2) “size (kb)” , round((num_rowsavg_row_len/1024),2) “actual_data (kb)”,
(round((blocks8),2) – round((num_rowsavg_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((blocks8),2) > round((num_rowsavg_row_len/1024),2)) order by 5 desc ) where rownum < 10;

set lines 170
set pages 10000
col owner format a30
col table_name format a30
col TOTAL_SIZE format 99999999999
col ACTUAL_SIZE format 999999999999
col FRAGMENTED_SPACE format 999999999999
select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks8/1024)),0) “TOTAL_SIZE”, round((num_rowsavg_row_len/1024/1024),0) “ACTUAL_SIZE”, round(((blocks8/1024)-(num_rowsavg_row_len/1024/1024)),0) “FRAGMENTED_SPACE” from
dba_tables where owner not in (‘SYS’,’SYSTEM’,’PERFSTAT’) and round(((blocks8/1024)-(num_rowsavg_row_len/1024/1024)),2) > 100 order by 8 desc;

To find percentage of table fragmentation

select table_name,avg_row_len,round(((blocks16/1024)),2)||’MB’ “TOTAL_SIZE”, round((num_rowsavg_row_len/1024/1024),2)||’Mb’ “ACTUAL_SIZE”,
round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2) ||’MB’ “FRAGMENTED_SPACE”,
(round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2)/nullif(round(((blocks16/1024)),2),0))100 “percentage” from all_tables WHERE table_name=’&TABLE_NAME’;

Leave a Comment