We have a table named MAM_ASSETS, one of whose columns is BLOB. We have the option to keep our files in BLOB form or store them on a file server and keeping the link somewhere in other column of MAM_ASSETS.
We provide the option to move data from BLOB column to file server that vacates space. BLOB column data is stored in some tablespace.
When we move the data from BLOB column to file server, the space in BLOB tablespace is not released. Is there a way to release this space?
alter table MAM_ASSETS enable row movement;
alter table MAM_ASSETS deallocate unused;
alter table MAM_ASSETS shrink space;
This will only work if the tablespace has segment space management set to auto. It's not a true reclaim of space, but in the right situation, it will. Check out doc ID 820043.1 on metalink, it's fairly comprehensive on this. Or check out http://www.dbasupport.com/oracle/ora...e_shrink.shtml