fragmented space
Team,
Please note: This for Oracle 11.2.0.4.0,
I have a high percentage of fragmented space. When I ran the below query against 4 tables,
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='MY_TABLE';
I got.
table_name total_size actual_size fragmented_space percentage %reclaimable space
my_Table1 324724 4760 319963 98 87
my_Table2 24014 995 23019 95 81
my_Table3 9174 208 8965 97 85
my_Table4 699 51 647 92 75
Which of the following do you recommend to get rid of the fragmentation.