How to reclaim storage for deleted LOBs

Question

I have a LOB tablespace. Currently holding 9GB out of 12GB available. And, as far as I can tell, deleting records doesn't reclaim any storage in the tablespace. (This was by the simple method of monitoring storage -- queries against user_extents, which is about all I'm allowed as a non-DBA) I'm getting worried about handling further processing.

My concern is simply running out of space -- we're at about 9 GB out of 12 GB available for the tablespace and I want to figure out how to reclaim space before asking for more.

The LOB columns are stored in a separate tablespace, though "storage in row" is allowed for small ones.

This is Oracle 11.1 and the data are in a CLOB and a BLOB column in the same table. The LOB Index segments (SYS_IL...) are small, all the storage is in the data segments (SYS_LOB...)

We'e tried purge and coalesce and didn't get anywhere -- same number of bytes in user_extents.

"Alter table xxx move" will work, but we'd need to have someplace to move it to that has enough space for the revised data. We'd also need to do that off hours and rebuild the indexes, of course, but that's easy enough.

Copying out the good data and doing a truncate, then copying it back, will also work. But that's pretty much just what the "alter table" command does.

Am I missing some easy ways to shrink things down and get the storage back? Or is "alter table xxx move" the best approach? Or is this a non-issue and Oracle will grab back the space from the deleted lob rows when it needs it?

Answer 1

This question is old, but never had a working answer and may be useful for lot of people so here the solution I found when facing this problem.

You're right, deleting the LOB won't reclaim any storage in the tablescape.

Running this query before and after deleting LOB will give the same result

select bytes
    from user_segments
    where tablespace_name = yourTableSpace
      and segment_name = yourTableName; 

Reading this Oracle doc, I found out that you need to execute the following statement

ALTER TABLE yourLobTable MODIFY LOB (yourLobColumn) (SHRINK SPACE);

Which will shrink a BASICFILE LOB segment.

Now if you re-execute the query

select bytes
    from user_segments
    where tablespace_name = yourTableSpace
      and segment_name = yourTableName; 

You will notice that the space have been released to the tablespace, and will be able to re-use that space as you wanted.

Now for those who would like to free up space on disk, note that the datafile won't automatically re-size and will still hold the full size on your disk.

But before re-sizing the datafile, make sure you deallocate unused space in a segment (stole it from the same doc as stated before) using these queries (use the one you need)

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

Note that the KEEP clause is optional and lets you specify the amount of space retained in the segment. You can verify that the deallocated space is freed by examining the DBA_FREE_SPACE view.

Now to re-size your datafile,

ALTER DATABASE DATAFILE yourDatafile.dbf resize 500M

Note that the maximum size you can shrink when resizing is based on the position of the last block of data in your datafile. Therefore there is a lot of chance that after a deletion of a lot of datas you can't really re-size. Then, the easiest way is to export the data and re-import in another tablespace, then drop the old tablespace. When importing the data, Oracle will pack them as much as possible.

If you want to free up even more space, you can clear your temp tablespace which sometime hold a lot of space (5GB for my database). Use the following statement to check it size :

SELECT tablespace_name, file_name, bytes
  FROM dba_temp_files WHERE tablespace_name like 'TEMP%';