How to claim back space in a tablespace or datafile?

[CaoCao]

I'm running statspack at 1 hour interval snapshot and stored in TOOLS tablespace. I notice that the space usage keeps increasing everyday even I deleted the old statspack data. How can I claim back the lost space? For your info, TOOLS tablespace is only used for statspack data. Tks.

[deepakc_in]

try coalecesing the tools tablespace

[fraze]

That wont do it - it will only coalesce free extents into one large extent. If you delete data from a table you wont see the freespace go down. The freespace is based on the High Water Mark. To reclaim the space you'll have to re-organsie the tables (export/import, move table etc)