Forum OpenACS Q&A: Re: Reclaiming space used by Oracle LOBs

Posted by Sebastiano Pilla on
You are mostly correct. AFAIK, emptying a column marks the previously occupied space available for other data, but this space is datafile space: in other words, when Oracle grabs some disk space from the filesystem, it isn't going to release it.

Since it sounds that you'd like to get some filesystem space back, what you could attempt on a copy of this database is:
alter database datafile 'blah' resize 500M;
or whatever size you wish. However, be aware that:
If sufficient disk space is not available for the increased size, or if the file contains data beyond the specified decreased size, Oracle returns an error.
So the other option is to move some the objects to another tablespace (via the alter table 'blah' move ... command) to free up space in the offending tablespace.

If it sounds very trial-and-error... well, it is. Not much else to do about that.