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.