title: How to reclaim storage for deleted LOBs
body: <p>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. </p>
  
  <p>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. </p>
  
  <p>The LOB columns are stored in a separate tablespace, though "storage in row" is allowed for small ones.</p>
  
  <p>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...)</p>
  
  <p>We'e tried purge and coalesce and didn't get anywhere -- same number of bytes in user_extents. </p>
  
  <p>"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. </p>
  
  <p>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. </p>
  
  <p>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?</p>