Reorganization Case Study 

Purpose

This case study shows how to perform a reorganization with the following aims:

·        Convert dictionary managed to locally managed tablespaces

·        Convert the tablespace layout to the new SAP standard layout with a single large tablespace, PSAP<SAPSID>.

·        Correctly process tables with LONG or LONG RAW fields, which cannot be reorganized using the online procedure – they require a reorganization using export/import. As of Oracle 10g, you can convert LONG and LONG RAW fields online to CLOB and BLOB fields. After this conversion, you can convert all tables online.

In this case study we process tables from a group of tablespaces – that is, tablespaces in the old tablespace layout. If you want to process all the tables in your database, we recommend that you do this in groups of tablespaces.

However, if the database is small, you can try processing all the tablespaces in one run.

Prerequisites

·        One of the aims of the case study is to convert dictionary managed to locally managed tablespaces, as shown in the following graphic:

Locally managed tablespaces are now the SAP standard. When you create new tablespaces with BRSPACE, they are by default locally managed.

·        When you reorganize a table tablespace, BRSPACE also reorganizes the corresponding index tablespace. For example, if you reorganize PSAPSTABD, then PSAPSTABI is also reorganized.

·        You might need to plan downtime for this procedure because tables with LONG or LONG RAW fields (if selected) require reorganization with the export/import procedure, which cannot be performed online.

Process Flow

...

       1.      You create a new tablespace called PSAP<SAPSID> for the schema owned by SAP<SAPSID>, which is locally managed and which stores both data and indexes:

¡        Set Data type in tablespace in the BRSPACE menu or command option –d|-data to both. This means the new tablespace will contain both tables and indexes.

¡        Set File autoextend mode in the BRSPACE menu or command option –a|-autoextend.

¡        Set the table data class of the new tablespace in the BRSPACE menu or command option –l|-class to the list of tablespaces to be reorganized or to allif you want to reorganize all tablespaces.

¡        Make sure that the tablespace is large enough to hold all the data from your existing tablespaces.

BRSPACE sets up the new tablespace, ready to contain the reorganized tables.

       2.      You reorganize the tables in the selected tablespaces using the BRTOOLS menus or the BRSPACE command option –s|-tablespace:

¡        Set Tablespace names to the tablespaces for which you want to reorganize tables. If you want to reorganize all the tables in a component, set Table owner in the BRTOOLS menu or BRSPACE command option –o|-owner to the name of the SAP owner. Set Table names in the BRTOOLS menu or command option –t|-table to “*” to avoid having to make a further selection.

¡        Set New destination (newts) in the BRSPACE menu or command option –n|-newts to the new tablespace, PSAP<SAPSID>. You do not need to specify a separate index tablespace.

¡        To improve the performance of the reorganization, you can set Parallel threads or command option –p|-parallel.

BRSPACE reorganizes the tables to the new tablespace and deletes the tables from the source tablespaces.

However, BRSPACE cannot reorganize tables with LONG or LONG RAW fields. It displays a warning message and leaves these tables in the source tablespace.

       3.      You stop the SAP system.

       4.      You perform an export/import to reorganize the remaining tables with LONG or LONG RAW fields (and associated indexes) into the new tablespace:

                            a.      You perform a dummy reorganization to generate Data Definition Language (DDL) statements with which you subsequently create the tables in the new tablespace:

§         Set Tablespace names or command option –s|-tablespace to the tablespaces containing the remaining tables with LONG or LONG RAW fields (and associated indexes).

§         Set Create DDL statements (DDL) in the BRSPACE menu or command option -d|-ddl to only.

§         Set New destination (newts) in the BRSPACE menu or command option –n|-newts to the new tablespace, PSAP<SAPSID>. You do not need to specify a separate index tablespace.

                            b.      You export the table data from the tablespaces:

§         Set Tablespace names or command option –s|-tablespace to the tablespaces containing the remaining tables with LONG or LONG RAW fields (and associated indexes).

§         Set Export dump directory or command option –u|-dumpdir to a directory with enough space to store the data from the exported tables.

                            c.      You drop the tablespaces (including contents using command option –f|-force) from which you have just exported the table data.

                            d.      You change to the directory where you stored the DDL statements – that is, $SAPDATA_HOME/sapreorg/<coded timestamp> – and enter the following SQLPLUS commands:

connect / as sysdba

@ddl.sql

This creates the empty tables in the new tablespace.

                            e.      You import the table data into the new tablespace.

       5.      You restart the SAP System.

 

For more information on reorganization see SAP Note 646681.