Check table fragmentation in Oracle

Check table fragmentation in Oracle

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. The space is available with table and table size grow even it has less data in its data blocks. For checking fragmentation we have following queries which will help to check fragmentation in between tables. If fragmentation is large in large size table it will lead to performance degradation also.

Check top 25 fragmented table in Schema
Enter schema name inwhich you find top 25 fragmented tables.

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 < 25;

Check in percentage of table fragmentation

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

Check the physical size of tables occupied in datafiles

Select table_name, round((blocks*8)/1024,2)||'MB' "size" from user_tables where table_name = 'EMP';

Select table_name, round((blocks*8)/1024,2)||'MB' "size" from dba_tables where table_name = 'EMP' and owner='SCOTT';

Check the actual size of table data present in it

select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from user_tables  where table_name = 'EMP';

select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from dba_tables where table_name = 'EMP' and owner='SCOTT';

4 thoughts on “Check table fragmentation in Oracle

  1. Brahadeesh Aniruthan

    Need clarification on the below.
    I see that block*8 is used in “Check top 25 fragmented table in Schema” script and block*16 is used in “Check in percentage of table fragmentation” script. If i use block*8 or block*16 on both the scripts by substituting it vise versa . I getting different values of percentage reclaim and also the space utilization for a same table. Could you please explain block*8 and block*16 purpose. can’t we use either block*8 or block*16 for both the scripts


  2. Saquib Arfeen

    You can use this query universally without finding the block_size of every tablespace you encounter.

    select a.owner,a.table_name,a.last_analyzed,a.avg_row_len,round(((a.blocks*c.block_size/1024/1024)),2)||’MB’ “TOTAL_SIZE”,
    round((a.num_rows*a.avg_row_len/1024/1024),2)||’Mb’ “ACTUAL_SIZE”,
    round(((a.blocks*c.block_size/1024/1024)-(a.num_rows*a.avg_row_len/1024/1024)),2) ||’MB’ “FRAGMENTED_SPACE”,
    round((round(((a.blocks*c.block_size/1024/1024)-(a.num_rows*a.avg_row_len/1024/1024)),2)/round(((a.blocks*c.block_size/1024/1024)),2))*100,2) “FRAG_PERCENT”, b.index_name
    from all_tables a, dba_indexes b, dba_tablespaces c WHERE
    a.table_name=b.table_name and
    a.tablespace_name=c.tablespace_name and



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.