Entering content frameProcedure documentation Reorganizing a List of Tables and Indexes Locate the document in its SAP Library structure

Use

You can use SAPDBA for Oracle to reorganize a list of tables or indexes.

Prerequisites

Procedure

  1. Create an ASCII file in the working directory with the list of the objects to be reorganized:

Example

Here is an example of a file containing a list of objects to be reorganized:

ACTFLI

ACTFLI~0

OWNIT ACTARC

The first two lines contain objects owned by sapr3 . The final line contains an object owned by OWNIT .

  1. Choose Reorganize list of tables and indexes.
  2. Enter List of tables/indexes.
  3. This is the name of the file that you created in the first step.

  4. Enter the Working directory.
  5. 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).

  6. Select one of the following reorganization methods for Data Transfer:
  1. If you are using the export/import method for Data Transfer, enter the Dump destination.
  2. 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.

  3. Select the required parameters for Data transfer:

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 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

Parallel

1

Exp/Imp

Reorganize with or without parallel processing

By increasing this to 2 or more, SAPDBA recreates the tables in parallel using the number of processes you enter. For more information, see Performing Processes in Parallel to Speed Up Reorganization.

ParTable

1

Create As Select

Reorganize with or without parallel processing

By increasing this to 2 or more, SAPDBA recreates the tables 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.

  1. Enter the required storage parameters under Storage parameters.
  2. 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 which are reorganized or which are 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).

SAP-Tsp

NO

Both

Move objects to SAP-standard tablespaces

If you set this to YES, SAPDBA places all objects to be reorganized in the tablespaces defined in the ABAP dictionary.

RebuildI

NO

Both

Use ALTER INDEX REBUILD [ONLINE] SQL statement

If you set this to YES, the indexes to be reorganized are set up with the above SQL statement, not with DROP/CREATE INDEX . The [ONLINE] option is used automatically if possible.

Advantages:

  • Old index is available while new one is being created
  • Faster reorganization
  • Constraints need not be deleted

Disadvantages:

  • More space required for extra copy of index
  • Freespace cannot always be optimally allocated
  • Can only be used when only indexes (not the accompanying tables) are being reorganized

ParIndex

1

Reorganize indexes with or without parallel processing

By increasing this to 2 or more, SAPDBA recreates indexes in parallel using the number of processes you enter. For more information, see Performing Processes in Parallel to Speed Up Reorganization.

  1. Choose Start to prepare for the reorganization.

SAPDBA prepares for the reorganization by:

  1. Reply to the prompts in the preparation phase as necessary.
  2. Finally, SAPDBA prompts you to start the reorganization.

  3. Choose how to start the reorganization:

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.

Leaving content frame