Reclaiming Unused Space in Datafiles…


Most of the time I write about stuff I’m interested in, but occasionally I’m forced into writing something to prevent me answering the same question again and again. This is one of those moments and it’s been several years in the making. The last time I tried to muster up the enthusiasm was on a flight last year and I got so bored I fell asleep.

Well today I finally got round to putting it together, so next time I get asked something like,

“I’ve moved a table to a new tablespace, how do I free up the space in the original datafile?”

I can answer, read this:

If you already know the answer, please don’t read it. I don’t want to be responsible for you falling asleep at your desk and losing your job. 🙂



Author: Tim...

DBA, Developer, Author, Trainer.

4 thoughts on “Reclaiming Unused Space in Datafiles…”

  1. Of course, the next question is “what about multiple data files?” Tablespace map isn’t too clear about which file unless you hover over particular segments. Not so bad if you have 5 files. 25 files or 250 files… and 1000’s of objects of varying sizes… a bit of a pain figuring out where and how much shrinkage can happen.

    I must say though, I find the map a useful visualization. It does necessitate a warning for newbies about not getting overly concerned about “fragmentation” when it shows black, red or yellow pieces scattered all over the place.

  2. Hi.

    I purposely steered clear of a few things to simplify the article and I didn’t want to mention the “fragmentation” word. 🙂



  3. Hi.

    Based on your concerns I’ve added a cautionary note at the start and a section about dealing with tablespaces with multiple datafiles.



Comments are closed.