Reorganizing a Tablespace and Data Files 

Use

You can use SAPDBA for Oracle to reorganize a tablespace with data files. It is only possible to use the export/import reorganization method for this procedure. The procedure allows you to consolidate a tablespace into the minimum number of data files. This type of reorganization is the most complicated and we recommend that you only perform it occasionally.

The procedure has the following features:

• You can change the default values for file size and file directory. Therefore, you can optimize disk space utilization through appropriate distribution of the files.

• 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.
• Make sure that no Oracle background processes are running. Hanging background processes can disturb the tablespace reorganization because they keep data files open. While these data files can be deleted during the reorganization, the disk space from such a file is not released by the operating system until the process is no longer keeping it open.

1. Choose Reorganize tablespace and data files.
2. Enter Tablespace name.

This is the name of the tablespace to be reorganized.

3. 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).

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



If SAPDBA uses the Oracle program export 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 is less error-prone than one to tape.

5. Select the required parameters for ORACLE exp/imp.

Parameter	Default	Meaning
ComprDmp	NO	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	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	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	Commit command passed to database once buffer data has been imported
Parallel	1	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	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	To accelerate the reorganization, we recommend providing at least 3 MB of buffer space.

6. Enter the required storage parameters under Storage parameters.
7. Enter the required Object handling parameters:

9. Choose Start to prepare for the reorganization.

• 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>.rtd log
• Scripts required to perform the reorganization
• restart.rtd file for restart in the <timestamp> subdirectory

• Immediately in the foreground
• In the background
• Not now, but possibly later

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