It is interesting to go through all the replies.
I do have experience in reclaiming LOB tablespace 8 months ago. We have Oracle 8.1.7 server.
We had tons of rows in a table having BLOB datatype. Though we deleted rows, the size of the tablespace did not change. Then we emptied the blob and did not give the results. As the other DBA said Oracle doesnt have straight forward method of getting rid of LOB data. We even consulted Oracle support on this. Even if you delete the rows, the LOB still hangs on to the tablespace and only the blob pointer is lost.
Then we exported the table data, disable the constraints, truncate the table, import the table data and enable the constraints. We successfully did this in all 5 environments including production and reclaimed the space from tablespace. It needs downtime to production and you dont need to drop and recreate the table.
But one theory from Oracle is, if you delete the rows, the LOB pointer is gone and subsequent LOB inserts will reuse the space which is not released from the tablespace.
Hope this will help the DBAs.
--Ravi
