Home > B2B, Database, Oracle > Shrink b2b lobs

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
  1. Bharat
    2011-11-28 at 05:57

    alter table tip_datastorage_rt modify lob (clobvalue_clob) (shrink space); does not work it errors out ==> cannot shrink clob_clob

    • 2011-11-28 at 16:04

      Strange, it works for me. I tested it right now just to be sure. Any additional messages or error codes, either in the user interface or in the database logs?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: