Skip to Content

LOB segment Large in size than Tables

Dear Experts,

I would like to know how to sort out the below issue.

The tables size is within a GB but those LOB segments are 17 or 15 GB in our server.

Tables are TST03,DYNPSOURCE,DWTREE,REPOSRC and etc.

The above four are top size LOB segments in the server.

Oracle release is 11.2.0.2.0

I had gone through SNOTES and forums.

Still i need more clarifications on how to avoid the growth in feature and best way to compress the largest LOB segments.

Thanks a lot.

Best Regards,

Pradeep

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 06, 2013 at 01:15 AM

    Hi Pradeep,

    For details on LOB refer SAP Note 500340 - FAQ: LOBS

    You need to regualrly monitor the top tables under DB02 transaction code. Look out for LOB segments in the top table list . Using the SAP note 500340 you may identify for which table does this LOB segment belongs. Later take corrective action against the LOB segments.

    For eg: If the LOB segment belongs to table TST03 ( spool table), you can perform reorg of TST03 table by delete older entries. Then you may simply shrink the LOB segment to reduce its size.

    Hope this helps.

    Regards,

    Deepak Kori

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 06, 2013 at 08:53 AM

    Hi Deepak,

    Thanks a lot for the Reply!!

    I had gone through the SNOTE.

    I have found the tables already and try to shrink directly by the below SQL command,

    alter table SAPSR3.DYNPSOURCE modify lob (FIELDINFO) (shrink space);

    After excution of above statement i could claim 64 MB (I have done this in Development system in offline mode the actual size was 1.3 GB after execution it reduced 64MB in the Database)

    Consider to production environment it may take more time for shrink because it LOBs are large in size.

    If i compress the whole database,it would be better?

    If i delete the old entries and reorganize the tables alone would be easy?

    or else should i follow what i did in development system?

    Thanks a lot !!

    Add comment
    10|10000 characters needed characters exceeded