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

Posted by Janine Ohmer on 06/09/03 07:30 PM

The programmer for one of the sites we host has moved the LOB data (images and documents) into the filesystem for improved performance. Now I want to reclaim the disk space they were using and Oracle doesn't make that easy.

When he started this project he was setting the LOB column's value to null. I knew that that was bad; it was leaving orphaned LOBs that Oracle would never clean up. I told him to start setting the value to empty_blob(), which is what I was told to do some time ago, but I have a nagging suspicion that this may be leaving orphans as well. It *should* be smart enough to see that there is already storage allocated and remove it, but I'm afraid that what is really going on is probably that it is allocating a new empty space and throwing away the pointer to the old one.

I would like to know the right way to do this, simply for education's sake; however, regardless of whether it is working properly or not, I expect we are still not going to be able to reclaim the space in the .dbf files without doing a full dump and restore to a new tablespace.

Am I generally correct here? It boggles my mind how brain-dead Oracle is about LOBs; the entire feature seems to have been programmed by an unsupervised group of junior programmers and then never looked at again.

thanks,

janine

2: Re: Reclaiming space used by Oracle LOBs (response to 1)

Posted by Sebastiano Pilla on 06/10/03 11:11 AM

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.

3: Re: Reclaiming space used by Oracle LOBs (response to 1)

Posted by jerry arns on 06/10/03 11:12 AM

Hello,

we did that recently.

We set the blob to null (i guess that empty_blob would also do the trick), export the table, drop it and import it.

Jerry

4: Re: Reclaiming space used by Oracle LOBs (response to 1)

Posted by Dirk Gomez on 06/10/03 12:41 PM

Which Oracle version? :)

what does alter table move say in your docs?

I'd rather do a create table as select * - quite likely also version-dependant when it comes to LOB handling - than an export/import.

5: Re: Reclaiming space used by Oracle LOBs (response to 1)

Posted by Janine Ohmer on 06/10/03 05:06 PM

This is version 8.1.7.4. Sorry I didn't mention that earlier.

What I remember from the last time I had to deal with this (a couple of years ago) is that although you can resize a datafile it doesn't work on a LOB segment, so the only way to do it is to recreate the table via one of the methods mentioned. I was hoping that I was remembering wrong, that it wasn't going to be quite this messy, but it sounds like I am unfortunately correct.

This table has lots of foreign key constraints; I"m not sure how well that will work with move, select into, etc. We can try it, but I'm not too worried about using exp and imp either. Maybe I've just been lucky but they've always worked well for me.

If anyone has any good shortcuts please post; otherwise I will get out my hip boots and prepare to wade into the muck. :) Thanks!

6: Re: Reclaiming space used by Oracle LOBs (response to 1)

Posted by Ravi Manne on 06/13/03 06:36 PM

It is interesting to go through all the replies.
I do have experience in reclaiming LOB tablespace 8 months ago. We have Oracle 8.1.7 server.
We had tons of rows in a table having BLOB datatype. Though we deleted rows, the size of the tablespace did not change. Then we emptied the blob and did not give the results. As the other DBA said Oracle doesnt have straight forward method of getting rid of LOB data. We even consulted Oracle support on this. Even if you delete the rows, the LOB still hangs on to the tablespace and only the blob pointer is lost.
Then we exported the table data, disable the constraints, truncate the table, import the table data and enable the constraints. We successfully did this in all 5 environments including production and reclaimed the space from tablespace. It needs downtime to production and you dont need to drop and recreate the table.
But one theory from Oracle is, if you delete the rows, the LOB pointer is gone and subsequent LOB inserts will reuse the space which is not released from the tablespace.
Hope this will help the DBAs.
--Ravi