Archive for June, 2010

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 or better, or alternatively that you have a one-off patch for the relevant bugs fixed in that release.

Categories: B2B, Database, Oracle