Use
You can use SAPDBA for Oracle to reorganize a list of tables or indexes.
Prerequisites
Procedure
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 .This is the name of the file that you created in the first step.
This is the working directory for the reorganization. The default is
This is the directory used to store the exported data. The default is specified by the
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 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.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. |
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 statementIf 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:
Disadvantages:
|
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. |
SAPDBA prepares for the reorganization by:
Finally, SAPDBA prompts you 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.