shrink space in tablespace

[mahajanakhil198]

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?

[skhanal]

How did you move the data from BLOB?. Did you update the column with empty space?

[ridges54]

Try this:

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

Hope this helps,

Jim