Changing Table Space of Existing Table from SE11

[naveen_inuganti2]

Hi,

I have a table with some data sitting across the Landscape including Production. Now, for some reason, if I want to change Table Space that was initially assigned to the Table from SE11, should I do it right away? Or Do I need to delete the Table and re-create it with New Table Space?

In fact, I have same question for the one that I created/activated in Development System but not yet applied to any other Systems? Do I need to simply change Table Space and Re-Activate it or should it be Delete and Create table with new Table Space?

I am worried about Table Re-Creation since this is going to purge the entire data, however this looks like the recommended way. Please share your thoughts?

Regards,

Naveen Inuganti

[Former Member · Accepted solution]

You wont be able to extend the table but the tablespace as the table sit on tablespace.

If you extend the tablespace then your space issue should be resolved as long you have sufficient freespace at OS level. There is no benefit moving the table from one tablespace to another as long as this table is IO expensive and you would like to store on separate disk but if you have SAN as storage then no need create separate tablespace.

[naveen_inuganti2 · Accepted solution]

Hi Sunil,

Thanks for your attention.

I understand what you are saying, but I think I am dealing with different scenario here. We really don't want to extend the existing Table Space or deal with it any more. We want to use one of custom Table Space that can be managed and controlled by BASIS. Based on this decision, we want to come-up with a process or a guideline for developer to use proposed custom Table space going forward.

I said going forward but we want to consider the stuff that is not yet fully deployed to/functioning in production (various projects). This is what I mean when I say, some of them are in Staging, Quality and Production with some data.

I think that very high level overview on scenario.. but hope where we are standing technically is clear.

Since BASIS cannot get into this adjustment every time, we are asking developers to do it from SE11. Now, the question is "For the Active Table with incorrect Table Space -- Do I need to delete and re-create table from SE11 or just change TableSpace and activate it?"

Regards,

Naveen

[ACE-SAP · Accepted solution]

Standard or custom tablespace can both be controlled by Basis.

Which option are you willing to change at tablespace level ?

Customer (and not custom) tablespace PSAPSR3USR is just there to hold customer specific tables...

Only few customer creates a dedicated specific tablespace layout, most of the time to handle VLDB (very large database).

Changing the tablespace used for a table should be done through SAP BR*Tools.

I do not see the use of interfacing BR*Tools through ABAP code.

Deleting / recreating the table is not the good way to change the tablespace, please take some time to read my answer and the provided links.

[naveen_inuganti2 · Accepted solution]

Thank you Yves KERVADEC - I will go through those links. btw, I was talking about PSAPSR3USR. Thanks for correcting.. Customer

Thx,

Naveen

[naveen_inuganti2 · Accepted solution]

Thanks for those links. That explained the whole process in great detail.

My question sounds like BASIS job (of course I am in SQL for SAP page though).. however, I want to understand one of the SE11 feature here..

but let me put my question with an example.

I have TableA created and activated with data class as APPL0 and moved to Quality system.

Now, I modified data class from APPL0 to USER from SE11 Table Technical Setting, which is different Table Space. If I move this Technical Setting Change to Quality System - what will happen there?

Will automatically changes Table Space of my TableA or BASIS involvement required?

Regards,

Naveen

[Former Member · Accepted solution]

If you change the data class i dont think the table is going to move in different tablespace as the table is already exist. You or basis team may need to perform the table move to different tablespace.

I dont think you should change the table data class so eailsy and as and when you want. I think You should be very careful about this and assigning the data class is not Basis task.

[ACE-SAP · Accepted solution]

Changing the data class won't have any impact at DB level on an existing object, it won't change the tablespace used to store an existing table.

It will only have an impact for tables that are not created yet at database level.

When changing the data class of an existing object, if you are willing to change the tablespace used to store that object, you will need to use BR*Tools at OS level to move the table to an other tablspace (reorg).

Regards

[manumohandas82 · Accepted solution]

There is a mapping of the Data class to the tablespace . ( Tables TA<DB> , IA<DB> eg :TAORA/IAORA ) , A change in the data class will not change  the table from already existing tablespace to new tablespace .

You may not require your BASIS team to move this as it can be done from SE14 as well .

If Basis team is doing this using DB methods make sure that they update tables IA<ORA> AND TA<ORA>

But i believe this TB movement is  neede  as i have seen issues during the upgrade ( especially when using SUM tool )

541542 - Upgrade phase INIT_CNTRANS: Container inconsistency

1805195 - Handling and troubleshooting of tablespaces during Upgrades, EhPs and SPs updates

Thanks ,

Manu

[naveen_inuganti2 · Accepted solution]

Hi Manu,

I tried SE14, this way:

After changing "Data Class",

from SE14--> Save Data-->Activate and Adjust Database. But I can still see old Table Space under SE14-->Storage Parameters-->TABLESPACE section.

Do you think I am missing something here?

Regards,

Naveen

[manumohandas82 · Accepted solution]

After making the change

SE14-->Storage Parameters

Extras -> Force Conversion

I am not able to test this in Oracle  as all the systems at my disposal is on DB6 . If the above doesnot work then you may request BASIS team to move the TABLE using BR Tools . After that you may check in SE14

Thanks ,

Manu

[naveen_inuganti2 · Accepted solution]

Above one is also not working for me.

I see a switch (button on appl.bar) at SE14-->Storage Parameter that is pulling data from "database" and "technical settings" separately. Table space is not same in my case.

Regards

Naveen

[janos_mucsi-besze]

Hi Naveen.

I see ou got a bunch of answers already. I can just repeat the previous answers.

The best way to do this, to use BRSPACE to reorganize( move) the table(s) from one tablespace to the other. BRSPACE will take care of any assignments internally in SAP system, so it will be fully consistent.

Regards,

János

[naveen_inuganti2]

Thank you Janos. In other words, this is something that can be done only or with help of BASIS team. ABAP developer, even though there is an option to change Data Class from SE11 and SE14 adjustment, cannot make any difference.

That make sense for various reasons. But, some how I see a little gap (may be in my understanding!) why changing "data class" from SE11 is not replicating at data base level.

Regards,

Naveen

[naveen_inuganti2]

Also, let me clarify: Now, I am very clear on how to handle the scenario. Just concern about - Whatever we are reporting at Technical Setting may not be correct space when you change initial assignment.

Though we have option of editing it from SE11, you should have some level of understanding on Data Base Table Space to know there is some additional work needs to be done for real change.

Sorry, if I am sounding like "want to do every thing from ABAP" - just checking about the option that allows developer to change data class without any validations/messages, when they are not really changing anything!

Regards,

Naveen

[janos_mucsi-besze]

Hi Naveen.

Data Class change is also something that is taken care by BR*Tools.

From my perspective reorganization is much more simple.

If you would change DATACLASS in SE11/SE14 directly, then you have to activate the table. If something goes wrong (Murphys law) you may ed up in an inconsistent DB state. Solving such an issue can be easy or difficult depending on in which activation phase the issue happened.

Regards,

János

[naveen_inuganti2]

If you would change DATACLASS in SE11/SE14 directly, then you have to activate the table. 

Activating table from? (SE11,SE14 or some where else?) I did it from SE14 and SE11 for a table - Table activated, Data is safe - but no changes to table space at data base level.

Regards,

Naveen

[janos_mucsi-besze]

Hi Naveen,

Changing the DATACLASS changes the assignment only in DD09L table, I think. A reorganisation is still required.

Regards,

János

[manumohandas82]

Dont you get an option in SE14->Storage Parameters->Extras ->Force Conversion ?

The table will get adjusted once you click this and confirm .

Dont think a reorg is required here

Thanks ,

Manu

[naveen_inuganti2]

Thanks to everyone for your help.

So, what I understand based on the above inputs and some research around this area is, It is not recommended to change data class from SE11 after initial assignment and activation. Even though you change it under technical settings, it is NOT going to change table space at SQL/Database level.
You should either delete and re-create the table or BASIS can do it with BR*Tools.

Closing this discussion with that note.

Regards,

Naveen

[ACE-SAP]

Hi

This could be done through a reorganization at operating system level using Br*Tools

brspace -u / -f tbreorg -o <sapowner> -t <tablename> -n <new tablespace>

This could be done either online or offline using option -mode

-mode online|offline

Offline can be faster than an online reorg but it locks the tables, so make sure that your SAP system is not running at reorganization time. Default: online

646681 - Reorganization of tables with BRSPACE

-f tbreorg - SAP Database Guide: Oracle - SAP Library

The action could be performed on all the system of you landscape without specific order as the tablespace used for a table is transparent for the SAP system.

Regards

[naveen_inuganti2]

Hi Yves KERVADEC,

Thanks for your help. I will check those details. Please check my response to Sunil and share your thoughts.

Regards,

Naveen