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';
Smart way of Technology 
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
LikeLike
You can check parameter db block size or block size of your tablespace
LikeLike
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
a.table_name=’&table_name’;
LikeLike
Thanks 🙂
LikeLike