Forum Stats

  • 3,741,292 Users
  • 2,248,405 Discussions


fragmented space

hishamawad Member Posts: 9 Blue Ribbon
edited Nov 13, 2020 10:24PM in SQL & PL/SQL


Please note: This for Oracle, not 12c.

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.

- alter table my_table1 shrink space;

- export a table, truncate a table, import the table.

- Move the table to another tablespace then move it back.

My questions:

1- Which method do you recommend?

2- Which method is the fastest?

3- Which method is generate less undo?

4- Can any of the above performed while the application is up?

5- Should I worry about the run time for my_table1 since it is 32GB.

Thanks a lot,



Sign In or Register to comment.