Shrink b2b lobs
Sites that use Oracle B2B 10 with reasonable volumes will eventually find that the B2B_LOB tablespace keeps on growing even if the built-in purge job is used. Why? The Oracle database doesn’t reuse the space from old (deleted) lobs. This is a known peculiarity, check other sources for details.
The fix is straightforward. Shrink space for the two lobs that store payload and wirepayload:
alter table tip_datastorage_rt modify lob (clobvalue_clob) (shrink space); alter table tip_datastorage_rt modify lob (blobvalue) (shrink space);
This can be scheduled as a recurring job or executed manually by a DBA and it can be done online without stopping B2B. To check the amount of storage used before and after, execute as b2b:
select round(sum(us.bytes/1024/1024)) total_mb from user_lobs ul, user_segments us where ul.segment_name = us.segment_name and ul.table_name = 'TIP_DATASTORAGE_RT';
For one system the storage used went from 5648M to 16M, quite a respectable saving.
According to Oracle Support (3-1865217281) it is safe to do this. It has intentionally been left out of the purge job, as it is considered to be a task for a DBA.
Note! Apparently one excellent reason to leave the shrink out of the purge job is that there is a bug in some old Oracle versions that may cause data corruption in lobs after a shrink. Check that you have 10.2.0.4 or better, or alternatively that you have a one-off patch for the relevant bugs fixed in that release.