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.

No comments: