bring down the number of tablespaces in Oracle 9.2.0.8

[Former Member]

We plan to reorginize our tablespaces to LMTS to about 10-15 tablepaces, currently we have 56 tablespaces with 200 datafiles.

We plan to use brtools to created the new LMTS tablespaces and move the tables (all with no long or long raw type of data) with

alter table xxx move tablespace new_tablespace_name;

and export/import the remaining tables with long and/or long raw columns.

My question is would SAP care about the tablespace name (obviously we'll have new tablespace names after reorg) as far as upgrade and transport are considered?

Did anyone have done those type of reorg before? I appreciate your feedback.

[Former Member]

Sharon,

We have done a similar exercise on our BW system. The answer to your question is NO, SAP does not care of the actual names as long as SAP has the tablespaces registered in its dictionary. It is a good practice to stick to the standard SAP naming convention for clarity.

We have used brspace to perform online and offline reorgs. The advantage of this process is that it does make changes to SAP dictionary when the change are done and it is important to cross check if the new tablespaces are registered in SAP tables before you start using the system, the change can be done manually using SQL or using SE16 in case brspace skips it.

I have listed important SAP notes to refer for this activity.

Note 646681 - Reorganising tables with BRSPACE

Note 46272 - Implement new data class in technical settings

Note 737598 - New BRSPACE options for control of Reorg and Rebuild

Cheers,

Nisch

[Former Member]

In order to avoid unnecessary pitfalls I would strongly recommend you to perform the ALTER TABLE MOVE reorganization using BRSPACE. This is possible with current BRSPACE patches as described in note 1080376 (3).

Furthermore you should also check manually the TABARTs stored in BI tables as described in note 771191.

Kind regards

Martin

[Former Member]

Thank you for posting your answers. Really appreciate it