Reorg of Table with LOB Segment

[Former Member]

Hello All,

We are planning to reorg the SXMSCLUP table in our PI SAP System.

System details are :

SAP Netweaver 7, SAP PI 7.0, Oracle 10G, Suse Linux

The table size is approx 61 GB with 5 GB index and Corresponding LOB Segment is around 400 GB.

I want to calculate the space required to reorg the tables. There is around 70 GB free space in Tablespce where the table SXMSCLUP belong.

Is it sufficient space to reorg the table.

I have also concern about LOBSEGMENT. Does Oracle also reorg/shrink the correspond LOB segment during the reorg of table.

If yes then which tablespace it use(e.g: PSAPTEMP), and how much approx space is needed.

Any help is appreciated.

Regards

Ashish

[former_member188883 · Accepted solution]

Hi Ashish,

For LOB segment management , try to find out which table is associate with the LOB segment.

Refer to SAP note below to identify the same

• SAP Note 500340 FAQ: LOBS

Based on the table identified you may perform required housekeeping activities and later shrink the LOB segment. Procedure of shrinking is explained in the SAP note above.

Secondly you can manage the growing table size for SXMSCLUP by performing archiving. Use the below notes

1538437 - PI auditing/performance tables are growing extensively

872388 - Troubleshooting Archiving and Deletion in PI

Hope this helps.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hello Deepak,

Thanks for reply.

I have gone through the mentioned notes, still have some doubts.

1) When doing the reorg of table SXMSCLUP, will oracle automatically shrinks the LOB segment also?

or i have to shrink the LOBSEGMENT seperately.

2) If yes, then does it uses any temproary tablespace like PSAPTEMP, or will it does in same tablespace.

3) Also approx how much additional space needed for shrink of LOBSEGMENT  ( I mean like reorg of table, we need approx 80% of space of table size should be free in tablespace. Is there any requirment like this)

Regards

Ashish

[ACE-SAP · Accepted solution]

Hello

Lob segment will be reorganized as well as the table it is related to.

It won't use space in temporary tablespace but depending on the scenario used (online or offline with export/import)  it would use space in the DB or in a specific FS used for export.

For an export/import method (to be used if you do not have that much free space in your DB) the export of the table is supposed to be half of the table size.

As stated by Deepak you should archive the tables before performing the reorg. It is a non sense to reorganize such very big table without archiving them first.

Regards

646681 - Reorganization of tables with BRSPACE

761713 - Some tables are not deleted after archiving

872388 - Troubleshooting Archiving and Deletion in PI

Tables SXMSCLUP and SXMSCLUR Archiving | SCN

[former_member188883 · Accepted solution]

Hi Ashish,

1) When doing the reorg of table SXMSCLUP, will oracle automatically shrinks the LOB segment also?
or i have to shrink the LOBSEGMENT seperately.

LOBSEGMENTS have to be shrinked manually using shrink table command.

Later you can reorg the table associated with the LOBSEGMENT.

2) If yes, then does it uses any temproary tablespace like PSAPTEMP, or will it does in same tablespace.

It will use the same tablespace as in which table exists

3) Also approx how much additional space needed for shrink of LOBSEGMENT  ( I mean like reorg of table, we need approx 80% of space of table size should be free in tablespace. Is there any requirment like this)

Shrinking activity will not require space like reorganization step. It will simply shrink the table.

Hope this helps.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hello Deepak,

Thanks for reply. It cleared my doubt.

I am starting with Online reorg process, as archiving is already done on this table.

What is recommended, first shall i reorg table and then shrink the corresponding LOB segment or vice versa?

Is anyone observerd the performance issue after shrinking LOBSEGMENT

Regards,

Ashish

[former_member188883 · Accepted solution]

Hi Ashish,

What is recommended, first shall i reorg table and then shrink the corresponding LOB segment or vice versa?

Firstly find out the table associate with the LOBSEGMENT using the SAP note above.

If the table is same for eg: SXMSCLUP, then reorg the table. This will reorganize the indexes as well.

Once the table is reorganized then shrink the LOB segement using shrink command.

Hope this helps.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Thanks a lot for ur input.

I will proceed as recommended.

[Reagan · Accepted solution]

The BR*Tools will take care of the LOB sements during a table reorg.

You do not need to do it seperately.

If you are doing an online reorg a copy of the source table and indexes with extension #$ will be created in the same tablespace.

Once the table has been reorganinsed it will drop the source table and the target tables will be renamed to the source name.

Regards

RB

[Former Member · Accepted solution]

Hello,

In that case can you please tell me about required free space calculation.

e.g :  Normally if we go for reorg table of 10 GB then we need a free space of 10 GB in that tablespace.

Here Table is of 61 GB + 400 Gb corresponding LOBSEGMENT, so do we need to consider whole space while doing reorg. Like do we need approx 460 gb free space while doing reorg of table or 65 Gb will suffuce?

Regards

Ashish 

[Reagan · Accepted solution]

Basically twice the space is needed temporarily for online reorganization because both source and target tables and indexes exist in parallel .

Table SOFFCONT1 should be cleaned as it is a candidate for one of the cleanups mentioned in this SAP note. There is a report to cleanup the table.

706478 - Preventing Basis tables from increasing considerably

The LOB is part of the table. if the size of the LOB is 400GB it doesn't mean that LOB contains 400 GB of data. What matters is the actual used space by the objects like the tables, LOBS, indexes.

Whether it needs the exact amount of space? Based on my experience I believe NOT.

During an Online reorganization, it used DBMS_REDEFINITION to creates the target table and copes the data from the source table.

It then drops the source table and renames the target table.

Regards

RB