LOB segment Large in size than Tables

[tamil_arasan]

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

[former_member188883 · Accepted solution]

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

[tamil_arasan]

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 !!

[former_member188883]

Hi Pradeep,

Shrinking the whole database will consume more resources and will take more time as compared to shrinking a single table.

As it is Production environment, I shall suggest you to perform table reorg while there is low load on the systems mostly during the night time. Ensure that your activities do not clash with database backup activities as it might delay your operations.

Hope this helps.

Regards,

Deepak Kori

[tamil_arasan]

Hi Deepak,

Thanks again!

In case the table is TST03 i can delete old entries and do reorg to claim the space but if in case the tables are DYNPSOURCE,DWTREE,REPOSRC we can't delete the old entries.

can i proceed directly reorg those tables?

Will it free the space?

Kindly help me.

Regards,

Pradeep.

[former_member188883]

Hi Pradeep,

You may directly perform reorg tables DYNPSOURCE,DWTREE,REPOSRC and check the statistics.

Hope this helps.

Regards,

Deepak Kori

[tamil_arasan]

Hi Deepak,

I will do reorg the tables and update you.

Thanks a lot for tremendous help!!

Thanks once again.

Regards,

Pradeep.

[Former Member]

    Hi,

    We are facing same issue... Please let's know how to reorg(online reorganization) the individual table. BRtools or Sql     Command.....    Please help....   What is the step by step process

Thanks

Sumit 

[tamil_arasan]

Hi Sumit,

I have done the online reorg the table TST03 and DYNPSOURCE separately using BRtools.

Please find attached steps for the same.

I have reclaimed around 1.5 GB (both the tables actual size were very less)

It will take time based on the size of the tables.it took 15min for both the tables because those are very less in size.

for more information check the linkhttp://help.sap.com/saphelp_nw70/helpdata/en/1b/4e8f0d38a8f4419436d608a36b6581/content.htm

Best Regards,

Pradeep

[Former Member]

   Hi Pradeep,

   Thankyou so much.....for sharing Online reorganization document. We have planeed reorg in sandbox, will let you the results soon......

Once again.... Thankyou very much.....

Regards

Sumit Jain

[tamil_arasan]

Hi Sumit,

If possible delete old entries from the table then do Reorg.

check if the table has LONG fields before start Reorg.

You may get good result in entire table space Reorg instead of Table Reorg.

Kind Regards,

Pradeep

[Former Member]

Hi Pradeep,

I advise you to publish Online Reorganization of table via Brtools in SDN documents with all errors you faced & recommendations.

thanks

Sumit

[tamil_arasan]

Hi Sumit,

Just have a look at below link.

http://help.sap.com/saphelp_nw04/helpdata/en/1b/4e8f0d38a8f4419436d608a36b6581/content.htm

SNOTE:646681

Hope this helps

Thanks,

Pradeep