Process of Table Fragmantation

Summary 

When you start doing deletes and updates constantly in a table, you may end up your table to helvetic cheese. 

An example follows how to make a table full of holes and start waisting space and of course performance when doing full table scans on it. 

1. Create the table OBJECTS.

CREATE TABLE OBJECTS TABLESPACE EXAMPLES 
AS SELECT * FROM ALL_OBJECTS;

SELECT COUNT(1) FROM OBJECTS;

2. The table OBJECTS has 208342 rows in my database and size 22MBytes

SELECT owner, segment_name, segment_type, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE', 'INDEX', 'TABLE PARTITION', 'INDEX PARTITION')
--AND TABLESPACE_NAME LIKE 'AR_DATA%'
AND SEGMENT_NAME LIKE 'OBJECTS%'
--AND OWNER = 'ERMIS'
--AND ROUND(bytes/(1024*1024),2) > 1024
ORDER BY bytes DESC;

3. Make a huge hole deleting all the rows and leaving just one!

DELETE FROM OBJECTS WHERE ROWNUM < 208342;

4. Guess what, the size still remains the same. You can find out executing the previous query. 

How to find out fragmentation and space waste in a table.
 
Calculate or estimate statistics for a table using DBMS_STATS.GATHER_TABLE_STATS it calculates the columns BLOCKS, EMPTY_BLOCKS, NUM_FREELIST_BLOCKS, so you have the true information (depends the estimation) about wasted space

SELECT owner, table_name, last_analyzed, ROUND((blocks * :db_block_size)/(1024*1024),2) size_MB,  
ROUND((empty_blocks * :db_block_size)/(1024*1024),2) EMPTY_MB, 
ROUND((empty_blocks * :db_block_size)/(1024*1024),2) +  ROUND((num_freelist_blocks * :db_block_size)/(1024*1024),2) UNUSED_MB,
ROUND(100*(ROUND((empty_blocks * :db_block_size)/(1024*1024),2) + 
ROUND((num_freelist_blocks * :db_block_size)/(1024*1024),2))/ROUND((blocks * :db_block_size)/(1024*1024),2),2) || '%' 
UNUSED_PCT
FROM all_tables 
WHERE blocks<>0 
--and tablespace_name like 'FA%'  
AND table_name LIKE 'OBJECTS'
--AND owner = 'HELPDESK' 
ORDER BY unused_mb DESC;

Tip:You must have statistics to be able to cal wasted space. u can use on of the following:

DBMS_STATS.GATHER_TABLE_STATS or
ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS; or
ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS SAMPLE x PERCENT;

If you don't have statistics on the table, or you don't want to compute them (because changing statistics is like playing with the fire) the is the solution of Dbms_Space.unused_space 

The solution to win back the wasted table space is reseting HighWaterMark 

5. How to reset HWM / remove fragmenation? 

For that we need to reorganize the fragmented table. We have 4 options to reorganize fragmented tables:

1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

I prefer by experience the FIRST (move table, rebuild indexes)