If I understand the process correctly, the coalesce combines any "freed" up extents into as many contiguous extents as it can. When the next extent is needed, oracle will scan the table looking for an extent of the size it needs. If your newly recored extents are smaller than your "next" size in the storage options, then those extents will not be used.

From my readings, the only way to truely recover that space is to 1) kick everyone off the system. 2) export the data in the DATA tablespace, telling it to compress the extents 3) Truncate all the objects, 4) reimport the data. You have to tell the import to ignore any creation errors since your objects exist. On import, it will start at the beginning of the tablespace and work its way forward. This is the method for an Oracle7 system.

I seem to recall that in 8I, you can do this on a tablespace level.

Oracle does not really have a good tool for "defragging" a tablespace.

Please, everyone jump in and correct me if I am wrong. No pride in authorship and always willing to learn a new trick.