Thursday, August 12, 2010

Deletion of LOB data Doesn't Free the Space

Just learned that in order to reclaim space after deleting LOB data, you have to do:


alter table lob_test modify lob (data) (shrink space);


Also came to know that the undo space also get stored in the lob segment itself.

Another method could be to use following command:

ALTER TABLE MODIFY LOB () DEALLOCATE UNUSED KEEP M;

If your LOB table is huge and takes ages to complete and consumes lots of CPU, then you can break the above command down into phases and deallocate specific extents.