Reorganizing a Single Table or Index (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Reorganizing a Single Table or Index

Use

You can use SAPDBA for Oracle to reorganize a single table or index.

Prerequisites

You have configured and started SAPDBA. Refer to Getting Started with SAPDBA.

You have completed the Preparations for Reorganization, especially Backing Up the Database for Reorganization.

You have shut down the Oracle database and then started it in restricted session mode.

SAPDBA logs the reorganization in Central Log reorg<DBSID>.log.

SAPDBA supports online reorganization based on the Oracle internal PL/SQL functions.

Procedure

Choose Reorganize single table or index.

Enter Owner and Table or index names.

The owner is usually SAPR3. You can use a wildcard to find the table or index, for example, by entering ACT% to find all tables or indexes beginning with the letters ACT.

If you want to move the table or index to a new tablespace, enter Tablespace name, which must follow SAP naming conventions.

SAPDBA checks the size available in the new tablespace and, if sufficient, displays the names of the original tablespace and the new tablespace. For example, PSAPBTABD ® PSAPDOCUD indicates that you are moving a table between the tablespaces shown.

When you move a table, SAPDBA also moves the indexes to the corresponding tablespace, PSAPDOCUI in the above example.

Enter the Working directory.

This is the working directory for the reorganization. The default is <SAPDATA_HOME>/sapreorg or <SAPREORG>, based on the environment variable SAPREORG. Logs and scripts are saved in this directory. For more information on <SAPREORG>, see Environment Variables (UNIX) and Environment Variables (Windows NT).

If you are reorganizing a table, select one of the following reorganization methods for Data Transfer:

Create As Select method using SQL statements (that is, CREATE TABLE AS SELECT)

Exp/Imp method using Oracle export/import

Note

With Online/offline reorganization you can choose whether to reorganize the table online. Online reorganization is the default when the SAP System is running, otherwise offline reorganization is the default.

If you are using the export/import method for Data Transfer, enter the Dump destination.

This is the directory used to store the exported data. The default is specified by the exireo_dumpdir parameter. If it is not specified, then the default is <SAPDATA_HOME>/sapreorg.

Make sure that the space available in the directories or on the tapes is at least as large as the total size of all the objects to be reorganized. SAPDBA warns you if there is not enough space available. You can still continue with the reorganization even if SAPDBA issues a space warning.

If you are exporting to tape, SAPDBA asks you to state the size of the tape. SAPDBA also checks in this case whether the data fits on the tape. SAPDBA can only use one tape drive for each export dump file.

Caution

If SAPDBA uses the Oracle export program for the export to tape, write errors are possible that the Oracle export cannot recognize. Therefore, it is essential that you back up the corresponding data before starting such a reorganization. Otherwise you might lose data during the reorganization. In addition, always use the SAPDBA option CheckExp: yes (see parameters below). In general, an export to disk causes fewer errors than one to tape.

Select the required parameters for Data transfer:

If you are reorganizing a table, SAPDBA displays the following parameters under ORACLE exp/imp or Create As Select:

Note

SAPDBA only displays the parameters relevant to the reorganization method – that is, export/import or create as select – that you have chosen.

Parameter	Default	Method	Meaning
ComprDmp	NO	Exp/Imp	Compress the dump file, only possible for export to disk If selected, SAPDBA sends the data to the export dump files using the UNIX compress command. Do not use this parameter for tablespaces with objects already compressed by the SAP database interface, since it has no advantages (there might even be disadvantages).
Chop	NO	Exp/Imp	Chop the dump file, not possible for Windows platforms Select this if the export dump files are larger than the maximum file size (normally 2 GB) for your operating system. SAPDBA then sends the export data to a chop tool, which splits the data into several smaller files.
CheckExp	YES	Exp/Imp	Check dump files, recommended especially if exporting to tape SAPDBA performs a read check after the export using the inx<TSP>.sql scripts.
Commit	YES	Exp/Imp	Commit command passed to database once buffer data has been imported
ParTable	1	Create As Select	Reorganize the table with or without parallel processing If you set this to 2 or more, SAPDBA recreates the table in parallel using the number of processes you enter. For more information, see Performing Processes in Parallel to Speed Up Reorganization.
Direct path export	YES	Exp/Imp	Export data directly, without using SQL area This improves performance because the data is physically written straight to disk. In general, we recommend it.
Buffer size	3000000	Exp/Imp	To accelerate the reorganization, we recommend providing at least 3 MB of buffer space.

If you are reorganizing an index, SAPDBA displays the following parameters under Index type:

NONUNIQUE or UNIQUE

NORMAL, NORMAL/REV, or BITMAP

If the index is non-unique, you can change the index type to any of the three types given above, that is NORMAL, NORMAL/REV, or BITMAP. However, if the index is unique, you cannot use BITMAP.

Enter the required storage parameters under Storage parameters.

Enter the required Object handling parameters:

Parameter	Default	Method	Meaning
HideTab	NO	Both	Hide the table, applies only to the reorganization of a single table If you set this to YES, all the tables that are reorganized or indirectly affected by the reorganization are temporarily renamed during the reorganization (that is, if their indexes are reorganized or referenced by foreign key from other tables to be reorganized).
DropTab	normal	Exp/Imp	Drop the table The default is to drop the table after the data export. If you choose defer, the table is only dropped after the data has been imported. Although this improves data security during the reorganization, it means that enough space is required for two copies of the table (that is, the old and the new). If SAPDBA detects that there is not enough space, it automatically switches to normal mode.
ParIndex	1	–	Reorganize the index with or without parallel processing By increasing this to 2 or more, SAPDBA recreates the index in parallel using the number of processes you enter. For more information, see Performing Processes in Parallel to Speed Up Reorganization.

Choose Start to prepare for the reorganization.

SAPDBA prepares for the reorganization by:

Prompting to make sure that you backed up the database

Warning you if the SAP System is still running

Checking for freespace

Checking for space in the dump file if you are using the export/import method

Generating the:

<timestamp> subdirectory in the working directory

<timestamp>.rsi log

Scripts required to perform the reorganization

restart.rsi file for restart in the <timestamp> subdirectory

Reply to the prompts in the preparation phase as necessary.

Finally, SAPDBA prompts you to start the reorganization.

Choose how to start the reorganization:

Immediately in the foreground

In the background

Not now, but possibly later

Result

On successful completion, SAPDBA displays a confirmation message.

If there has been an error, SAPDBA displays the appropriate error message. You can restart the reorganization after fixing the problem.