Table Reorg - BR1962W Table has more than 255 columns

[Former Member]

Hi ,

We are performing Tables reorganization on our Oracle 11g database, SAP is ECC 6.06 and OS - HP UNIX.

During reorg, We are getting below message for tables and brtools is skipping these tables -

BR1108I Checking tables for reorganization...

BR1962W Table SAPR3.MLREPEKPO has more than 255 columns

BR1967W Table SAPR3.MLREPEKPO would be implicitly decompressed during reorganization

BR1111I Reorganization of table SAPR3.MLREPEKPO will be skipped

BR1962W Table SAPR3.PA0701 has more than 255 columns

BR1967W Table SAPR3.PA0701 would be implicitly decompressed during reorganization

BR1111I Reorganization of table SAPR3.PA0701 will be skipped

BR1962W Table SAPR3.RSEUMOD has more than 255 columns

BR1967W Table SAPR3.RSEUMOD would be implicitly decompressed during reorganization

BR1111I Reorganization of table SAPR3.RSEUMOD will be skipped

BR1962W Table SAPR3.T5ITT7 has more than 255 columns

BR1967W Table SAPR3.T5ITT7 would be implicitly decompressed during reorganization

BR1111I Reorganization of table SAPR3.T5ITT7 will be skipped

BR1962W Table SAPR3.TLVC_DFT_ASSGN has more than 255 columns

BR1967W Table SAPR3.TLVC_DFT_ASSGN would be implicitly decompressed during reorganization

BR1111I Reorganization of table SAPR3.TLVC_DFT_ASSGN will be skipped

Please suggest what is the reason of these tables skipping during reorganization and how can we perform reorg on these tables.

One more thing - Table DBTABLOG is runninf from almost 12 hrs and its end date is showing as 5th May of reorgnation, This table size is 180GB .

Somehow can we expedite this table reorg and do we have some alternate for a single table like export/import which can be used.

Please suggest.

Regards,

Shivam

[JamesZ · Accepted solution]

Hi Shivam,

As per SAP note 1436352, table with more than 255 columns won't be compressed. So the brtools

will decompress the table first, then compress normally. However table with more than 255 columns won't compress, so brtools omit the reorganization.

So above explains why brspace skipped the tables. However if you really want to reorganize the
tables, you can try below command:

brspace -u / -c force -f tbreorg -s <tablespacename> -

t <tablename> -e <num> -p <num>

or

brspace -u // -c force -f tbreorg -s <tablespacename> -

t <tablename> -e <num> -p <num>

If above command does still show the warning, then you have to use export/import as alternative way.

Best regards,
James

[Former Member · Accepted solution]

Hi James,

Thank you for reply, Tried the option but it did not work.

We want to move tables to different tablespace also as to change FS of larger size.

1) For Table Export/Import, Can you suggest how can we execute this process -

Should we export first, Drop table with structure->recreate in new tablespace and then do import.

If this is the process then how can we get Table and its Index structure statements to create them in new tablespace.

2) Can we use "Alter table move tablespace" command to move these 4-5 tables to new tablespace, Can we use this command when system is online.

What are steps required in both these cases.

Regards,

Shivam

[JamesZ · Accepted solution]

Hi Shivam,

You are welcome!

In that case, both of the methods need SAP offline. However every SAP table has data class which is the label "SAP" believes the a table stored in which tablespace. If we use pure database method to move a table to another tablespace, then the data class won't be changed, so there will be mismatch

between SAP layer and database. This mismatch won't affect live system, but it affect system copy
in the future.

So I would like to recommend you to decompress the tables and then use brtools to reorganize the new tablespace. Also please remember please use brtools to create new tablesapce, then data

class will be modified in abap layer. After reorganization you can perform the compress for the

table again, however table more than 255 columns may not be suitable to compress, that is the reason why we have the limit as mentioned in the previous SAP note.

So some points need to remember:

1. every SAP table, it has data class (SE11-> Table Name -> Technical Setting, can see the data

class)

2. then TAORA, IAORA defines which data class assigned to which tablespace

3. we should always create new tablespace via brtools, which will updated data clasee (TAORA) as well

4. better to use brtools to perform reorganization, especially move table to another tablespaces, so data class will be udpated

5. table with more than 255 columns may not be suitable to compress

Best regards,

James

[Former Member · Accepted solution]

Hi James,

We are moving tables from PSAPSR3 to PSAPSR3_NEW and they will be moved back to PSAPSR3 after activity, Purpose of this activity is to move tablespace to 500 GB FS from 70GB FS.

Which method you suggest for these tables in this scenario.

We have created new tablespace by brtools only.

You said, We can decompress these tables and then use brtools to reorganize. Can you suggest how can we decompress these tables. Also these tables are of very less size in KBs. So in that case you think if we decompress then use brtools for reorganization and then we can leave the tables without compression.

Please suggest.

Shivam

[Former Member · Accepted solution]

If we use move tablespace command at OS Level then do we need to take any action at SAP level to make SAP system working without affecting users, as tables will be moved back to PSAPSR3 after activity.

[JamesZ · Accepted solution]

Hi Shivam,

If you move data file from OS level, then there is nothing to do with SAP, which means there won't
be change needed. But during movement, SAP needs offline.

However to decompress the tables, you can use brtools as per SAP note 1431296 via decompress

option.

By the way if you followed steps PSAPSR3 to PSAPSR3_NEW to PSAPSR3, data class may change

finally, because brtools will always find the first data class mapping in TAORA (APPL0, or APPL1, etc.). However that won't be a problem.

Best regards,

James

[Former Member · Accepted solution]

Hi James,

We have created new tablespace PSAPSR3_NEW from brtools but I think that wont change table class unless you move tables by brtools So thinking if we execute SQL statement what impact will be on SAP level. Does it will be only used during next system copy or it will impact running system also.

Does SAP recommends to use SQL statement to move tables to new tablespace.

Can we perform decompression online, Will it impact running system and can we leave these tables decompressed after reorg.

Regards,

Shivam

[Former Member · Accepted solution]

Hi James,

I tried with decompress with reorg(dtab_Only) and it worked.

Can you suggest me if we can perform this decompression and reorg at same time and when system is online.

Do we need to compress this table back after activity or it does not have any impact.

Regards,

Shivam

[JamesZ · Accepted solution]

Hi Shivam,

Brtools will write any entry to TAORA table if you create tablespace with it.

As mentioned, SAP does not recommended to move table to another tablespace with sql command,
because of data class. If you really need to do so, you have to update data class for the corresponding tablespace as well.

As per SAP note 1431296, we can perform reorganization and compress/decompress online. If you need to compress the tables back it depends on different landscape, however for best practice, SAP

does not recommend compress tables that have more than 255 columns.

Best regards,
James

[Former Member · Accepted solution]

Thank you James for clearing my doubts.

Just one last question, We have one table DBTABLOG of size almost 200 GB in system which is running for reorg now.

We are running with parallel degree 32 still table reorg is slow, its only 3-4 GB space move per hr to new tablespace.

So that will take approx 50 hrs for reorg of this single table and this is only single big table left, rest are small tables.

Do you suggest any other way to speed up reorg process.

Regards,

Shivam

[JamesZ · Accepted solution]

Hi Shivam,

You are welcome.

The most time cost on reorg is IO. From database point of view, there is no way to tune it.
However if you have IO/hardware colleagues around you, you can try to consult with them. Check if
there improvement on IO or if hardware is fully used.

Best regards,
James