I need to delete a lot of small records from LOB-tablespace then load some larger ones. How can I avoid the fragmentation?

The Oracle (tm) Users' Co-Operative FAQ

I need to delete a lot of small records from LOB-tablespace then load some larger ones. How can I avoid the fragmentation?

LOB-records can be loaded only in flat free part of tablespace the same size or bigger. Small chunks scattered over all tablespace will not used. Also there is no way to defragment current LOB-tablespace online. You can avoid fragmentation by moving all data to another LOB-tablespace by clause 'ALTER TABLE .. MOVE another_blob_tspace' then drop original tablespace or by routine procedures export\truncate tablespace\reimport.

Further reading: Oracle8i LOB

Back to index of questions