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

A clarification, please. Did you specify a separate tablespace for LOB storage ? Something like:

create table t69
    (
        id_revision_number number(38,0)  not null
        , some_date date 
        , comment_text clob 
    ) logging
    tablespace table_data
    lob (comment_text) store as basicfile t23_comment_text (tablespace lob_data )
/

Also :

"as far as I can tell, deleting records doesn't reclaim any storage in the tablespace."

How are you monitoring space usage?

Answer 2

Generally, once an extent is allocated to a table, it stays allocated. Empty space within a table can be re-used if additional data is inserted into the table. However it is hard to reclaim space from within a table because the table may have, for example, blocks 1 to 100, and the empty blocks are in blocks 50-75, or the empty blocks are 1,3,5,7 etc.

The question is, are you worried about space being re-used within the table or do you need the space released for other objects in the tablespace, or do you need to be able to shrink the tablespace datafiles ?