StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to reclaim storage for deleted LOBs
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload