I have encountered a problem with oracle database that it does not reuse lob data. So, if you have 5000 rows of some lob data, and you delete them from the table, then insert the same 5000 rows, you will have 10000 rows of consumed space. That could be done easily with shrinking the table but the default tablespace segment space management (SSM) on oracle 10g is manual, so it does not allow row movement and table shrinking.
First, you create a tablespace with ASSM, that is automatic segment space management:
Now we can proceed with table movement and after that lob_segment movement. There are some steps we should follow:
- Move table
- Rebuild indexes (and move them to the new tablespace if you wish so)
- Move the lob segment
- Enable row movement
- Shrink the table and lob segment
Here is the sql needed, of course, you will need to change and/or add indexes that reside on the current table.
Table and the lob segment will shrink during migration, but just in case you need further space shrinkage i have included the scripts needed.