How to asses table fragmentation
Will someone please help me understand this? Oracle database 12.2.0.1. I've seen the following query all over the Internet for identifying table fragmentation.
select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table 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 "percent fragmented"
from dba_tables;
When I run this query for table SAPT, my understanding is that if I reorg this table, then column 4 'wasted space kb' is the amount of space I should expect to get back. Although in the example below that is not the case. FYI, I gathered stats before and after the reorg.