Forum Stats

  • 3,752,193 Users
  • 2,250,468 Discussions
  • 7,867,747 Comments

Discussions

fragmented space

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

Team,

Please note: This for Oracle 11.2.0.4.0, 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,


Hisham

Answers