Changing the Storage Parameters Manually

You can also manually change all the SAPDBA default values. To do this, select Change storage manually: yes.

When you select all the objects to be reorganized or exported/imported, a screen mask appears in which you can change the storage parameters as required. SAPDBA will then use these changed values for the corresponding process.

• Form the object list, you can select any objects for processing (next object|table|index, i.e. all objects or only tables or only indexes).

• Tables and indexes can be displayed selected in any order using any number of letters in their name (name |, where % stands for the LIKE function.

• You can restrict the object type ( type ||).

  You can display and/or manually change the storage parameters of the objects you select. If the name selection leaves out tables or indexes, these will automatically be assigned the SAPDBA default values (in accordance with the options selected for Compress Extents, Reduce object size or Use ABAP/4 Dic.for NEXT).

  To exit the display, select continue export/reorganization (the configured values are used in further operations) or quit (the process is terminated).

Reducing the Size of the Table or Index

You may want to reduce the size of a table/index (or the size of all objects in a tablespace) during a reorganization or for an export/import if a relatively large amount of memory space is required and occupied (allocated), but is not used by the relevant objects.

Choose Reduce object size: yes, if you want SAPDBA to automatically try to reduce the size of the objects (allocated storage space) during the reorganization or for an export/import. To determine the actual storage space occupied, SAPDBA uses the ORACLE command ANALYZE TABLE ESTIMATE STATISTICS or ANALYZE INDEX VALIDATE STRUCTURE. Using these values and the current storage parameters, a new value is determined for the space to be allocated. If this value is smaller than the storage space allocated before the reorganization or export/import, the size of the object is reduced to this value.

Checking Freespace

During each reorganization, SAPDBA checks whether all reorganized objects can be restored with their data in the appropriate tablespaces. Besides the size of INITIAL and NEXT, the number of extents expected is also taken into account (according to MINEXTENTS and the amount of data to be imported). This corresponds to the default of the option Check space: for ALL extents

In certain exceptional cases (particularly when the option Compress Extents: no and/or Reduce object size: no is set), the memory space check may be too restrictive. This means that SAPDBA states that there is too little freespace although there would be enough space.

In such cases, the option Check space: ONLY first MINEXTENTS extents can be set. This means that only the INITIAL extent or the extents according to MINEXTENTS are included in the storage space check. Other NEXT extents are not taken into consideration in the space check.

If additional NEXT extents are generated during the import/load and the import/load terminates due to lack of space, the import/load can be repeated with restart after the tablespace has been increased.

Limitations of the Database System

Reorganization may also be necessary to avoid problems related to the limitations of the database system or operating system. This can involve the following limitations:

• Number of extents per table/index (MAXEXTENTS) - software limit.
  Default: 100 extents.

The SAP tables and indexes are created with the parameter MAXEXTENTS=100, which specifies a maximum number of 100 extents (with a block size of 8 KB). If a table or an index reaches this limit, the database cannot create an additional extent for that object. In this case you should first increase the maximum number of extents to keep the system executable. You are recommended to increase the NEXT parameter ( Command Option -next) to avoid reaching the newly set soft limit.

See: Changing MAXEXTENTS and NEXT.

A reorganization of the object should only take place in an emergency.

• No. of extents per table/index (MAXEXTENTS) - hard limit.
  Standard: 505 extents.

This limit only exists for ORACLE releases <>.

At 505 extents the database system reaches a final, unchangeable limit for the no. of extents of an object. This limit depends on the block size of the database (the value of 505 extents applies to the block size 8KB). If you reach this limit the affected table or index must be reorganized.

Block size	Maximum for MAXEXTENTS	SAP-value for MAXEXTENTS
2 KB	121	100
4 KB	249	100
8 KB	505	100

The standard setting for parameter MAXEXTENTS = 100 should enable the administrator to counter a great increase in the number of extents in good time. This prevents you from reaching the hard limit and avoids reorganization.

• Maximum number of files per database - soft limit.
  SAP default: 254 files.

The database system only supports a specific number of data files in the database, dependent on the host system. If your database approaches this limit, you could reduce the number of data files by reorganizing tablespaces that have more than one file.

This is not likely to occur, however, as up to 254 data files can be created (R/3 System default after installation). The absolute upper limit (hard limit) is a maximum of 1022 data files, depending on operating system (AIX: 256 files; all other UNIX systems: 1024 files; ORACLE itself requires two files).

The threshold value for data files is specified by parameter db_files in profile init.ora . It can be increased to a maximum of MAXDATAFILES, the value of which was specified when the database was created. MAXDATAFILES itself should be less than the permissible maximum number of open files supported by the operating system. The default setting for both these parameters after installation is 254.

Because the possible number of data files is quite large and, as a result, will not be reached very quickly, reorganizations with the aim of reducing the number of data files should not be performed unnecessarily often.