I've heard the argument that you don't need to worry about fragmentation with Oracle and Locally managed tablespaces. However, I have come across a real world situation where it needed to be dealt with. The situation involved areference table that was being re-populated in full each night and it had grown to several times the size than was required (288MB vs 44MB). Since the table was a lookup table it was being joined with much larger tables in queries and these were wreaking havoc on the database.

In essence, the table involved was synchronized with an external source via the following process:

• Begin transaction
• Delete all rows from the target table
• Select all rows from the source and insert into the target table
• Commiit transaction

 After dealing with the situation I decided to be proactive about detecting the situation moving forward. To do so, I wrote the following query:

select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"
 from all_tables
 where
  ((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)) > (num_rows*avg_row_len/1024/1024)
  and  owner not in ('SYS', 'SYSTEM')
  and ((blocks*8/1024)) > 10
  

The above query can be used to detect tables that have more wasted space than actual used space and that are at least 10MB in size (you can adjust as needed).

After detecting tables as such you can use the ALTER TABLE... SHRINK space command on the table(s). You need to enable row moviement on the tables in order to run the SHRIN SPACE command.

i.e.

ALTER TABLE ENABLE ROW MOVEMENT

ALTER TABLE SHRINK SPACE