SAPDBA: Reorganization Menu 

Select the required reorganization type (Reorganize single table or index, Reorganize list of tables or indexes, Reorganize tablespace, Reorganize tablespace and data files or Move/rename data files of a tablespace). Note the differences in the menu options depending on the selected type of reorganization.

The following menu is displayed:

a

- Tablespace

d

- Working directory

e

- Dump destination

f

- Export/import

g

- Storage param.

h

- Object handling

 

Compress

  

ComprExt

  

HideTab

 

Chop

  

SAP-NEXT

  

SAP-Tsp

 

CheckExp

  

CheckExt

  

RebuildI

 

Commit

  

ReduceOb

  

ReduceFi

 

Parallel

  

Manually

  

ParIndex

 

- Start

      

DropTab (only for single table)

s

- Return

 

If you choose Move/rename data files of a tablespace, only menu options a, d, s and q appear.

If you want to use the SAP tool unload/load instead of Oracle export for exporting, you can set this with the menu option -f .
For more information, see SAPDBA: Expert Menu, SAPDBA unload/load, SQL*Loader, Reorganization with Create Table...as Select.

The most important menu items are listed below:

Enter the name of the object/tablespace to be reorganized and if necessary, its owner or the name of an object list. You can generate lists of objects (tables, indexes, tablespaces) from which you can select the required object by entering like values (for example T%, T%_0, PSAP%).

If you want to edit a group of several objects, an ASCII file must have been created in the working directory with the required object list. There may only be one object name on each line. If SAPDBA finds several definitions in a line, the first value will be interpreted as the owner of the second object specified. All other entries in this line are ignored (valid separators between the owner and object: spaces or dots). If there is only one entry, it will be interpreted as an object name. The owner of this object is automatically sapr3.

If a different target tablespace is required, you can specify it here. This option only appears for the reorganization type Reorganize single table or index.

Working directory in which the scripts and logs are to be saved. You can change this working directory if required.

The target directories or tape devices for the export dump files; the space available in the directories or on the tapes must be at least as large as the objects to be reorganized (table(s), index(es), tablespace). All further scripts and logs are always written to the working directory.

The export or import can be run in parallel by defining the parameter exp_imp_degree > 1 (See Parallel Export/Import).

SAPDBA warns you if there is not enough space available. You might want to continue with the reorganization nevertheless. The data written to the directories on the disk can be compressed by a certain factor by using the option Export/import method ® Compress dump file(s): yes (if ORACLE Export/Import is used). For security reasons, the space check does not take the affects of compression into account (the compression rate and degree of filling is not known exactly).

The menu option Reduce object size: yes is used, the export dump is computed more exactly or exactly (choose: estimate or compute statistics) since SAPDBA determines the storage space actually occupied by the object data and carries out the space checks using this value. The compression rate is, however, also not taken into account here.

When you export to tape, SAPDBA asks you to state the size of the tape. SAPDBA also checks in this case whether the data to be reorganized has space on the tape(s). SAPDBA can only use one tape drive for each export dump file. You are warned if the total amount of space required for a dump file is greater than the tape size.

If SAPDBA uses the Oracle program export for the export to tape, write errors are possible which Oracle export will not recognize. It is, therefore, essential that you back up the corresponding data before starting such a reorganization; otherwise you could lose your data during the reorganization. In addition, always use the SAPDBA option CheckExp: yes. In general, an export to disk will be less susceptible to errors than one to tape.

Here you can decide which methods you want to use for the data transport and set some parameters if required.

SAP unload / ORACLE SQL*Loader (as of ORACLE 7.2)

SAP unload / SAP load

Create table... as select (not for tables with long columns, or for reorganizations of tablespaces with data files).

For more information, see SAP unload/ load, SQL*Loader.

- Buffer size: Default: 3000000

To accelerate the reorganization, we recommend providing at least 3 MB of buffer space for the ORACLE Export and Import programs or for the load programs.

- Compress (Compress dump file): Default: no

Select yes if the data is to be compressed during export. In this case, SAPDBA sends the data with the UNIX command compress before writing it to the directories for the export dump files. By compressing the data, you can reduce the disk space required for reorganization by a certain amount (depending on the compression ratio).

Compression is only possible for an export to hard disk (with ORACLE Export). This option will be ignored if you export to tape. Therefore, use tape stations with hardware compression where possible.

Do not use compression for tablespaces containing objects already compressed by the SAP database interface (see Tablespaces with Compressed Objects), since there is no advantage to be gained (there are more likely to be disadvantages).

- Chop (Chop dump file): Default: no

This menu option only appears if the parameter chop_util_name is entered in the profile init<DBSID>.dba.

Select yes if the export dump files are larger than the maximum file size (usually 2 GB) for the particular operating system. In this case, SAPDBA sends the exported data to the chop tool via a named pipe. The chop tool splits the export dump files into several smaller files.

If both Compress dump files = yes and Chop dump files = yes are selected, compression takes place within R3chop (option -c ). The chop option does not exist for SAP load/unload or for Windows NT.

- CheckExp (Check dump files): Default: yes

After the ORACLE export, SAPDBA performs a read check if you select CheckExp: yes (test import with the Oracle parameter Indexfile). The inx<TSP>.sql scripts are created. This option should be used in particular if you are exporting to tape.

- Commit (Commit during import/load): Default: yes

If you use the default setting yes, the COMMIT command is passed to the database once the data in the buffer has been imported.

- Parallel (Parallel export and import): Default: degree 1

The default value corresponds to the value of the parameter exp_imp_degree in the profile init<DBSID>.dba. The export/import can be performed in parallel by selecting a degree of parallelism >1 (see Parallel Export/Import).

- ParTable (Parallel table creation): Default: degree 1

Exists only when using the method Create table... as select.

By choosing a degree of parallelism >1 you can perform the creation of the tables in parallel. This option is identical to the option ParIndex in Object handling. (See Performing Processes in Parallel).

For more information, see SAPDBA: Expert Menu.

You can make various settings for the storage parameters here:

- ComprExt (Compress extents): Default: yes

- SAP-NEXT (Use ABAP/4-DIC for NEXT): Default: yes

- CheckExt (Check space): Default: for all extents

- ReduceOb ( Reduce object size): Default: no

- Manually ( Change storage manually): Default: no

If you reorganize tables, indexes or a tablespace, you can change the default SAPDBA storage parameters for the individual objects (see Options for Changing and Checking the Storage Parameters and Changing the Extent Size).

- SAP-Tsp (Use ABAP/4-Dic for tsp.): Default: no

You can move an object to another tablespace (Target tablespace) during a reorganization. This option, however, can only be used if a single table or a single index is reorganized. You can use the option Use ABAP-Dic for tablespaces: yes to place the objects in the tablespace defined in the ABAP Dictionary (see Moving a Table or Index).

- RebuildI (Alter index rebuild): Default: no

During all pure index reorganizations, that is when an index is reorganized without its table or when not all the tablespace is reorganized with data files, the command Alter Index Rebuild can be carried out instead of deleting and setting up again with Drop/ Create Index. The advantage is that the old index remains accessible, whilst the new index is set up in a temporary segment. The old index is only deleted when the new index has been completely set up. This accelerates the new setting up of the index. It is also unnecessary to delete any constraints which cause the index to be accessed when they are being checked. Note that, temporarily, there has to be enough space in the relevant tablespace for the old and the new index at the same time. In addition to this, the tablespace’s free space cannot be combined optimally.

Indexes whose table is also being reorganized or whose tablespace, including data files, is being reorganized, (that is, those who are deleted together with the table by the command Drop Table, or deleted together with the tablespace by the command Drop Tablespace... Including Contents), are always recovered with Create Index. The option RebuildI is ignored in this case.

- ReduceFi (Reduce data file size): Default: no

If a tablespace with data files is reorganized, you can reduce the size of the data files using this option. SAPDBA determines the space actually required for the tablespace objects after the reorganization. SAPDBA suggests a size per file which is computed from this value and a 10 percent safety margin. You can increase this value if you expect the tablespaces to grow considerably.

- ParIndex (Parallel index creation): Default: degree 1

You can create indexes in parallel (see Performing Processes in Parallel). This is advisable if you are using more than one CPU (see also ParTable).

- DropTab (DropTable): Default: defer

This option applies only to the reorganization of a single table (Single Table HideTab (Hide tables during reorg.): Default: no

If you set this value 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).

- Reorganization The table is not deleted directly after the data export that precedes the creation of the new table, it is merely renamed. The renamed table is deleted after the data has been reimported. This procedure improves data security, however it does mean that there has to be space for the old and the new table in the tablespace at the same time. If SAPDBA realizes that there is not enough space in the tablespace, it switches automatically from Deferred mode to normal mode while it is creating the scripts. When a table is transported into another tablespace there are no space problems, despite the fact that the old and new tables exist at the same time. In this case, the Deferred mode cannot be changed.