Limitations of the Oracle Database System (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Limitations of the Oracle Database System

Locate the document in its SAP Library structure

Definition

The Oracle database has the limitations described in this section. The parameters MAXEXTENTS , DBFILES , and MAXDATAFILES are discussed.

Use

You need to be aware of the limitations described below when you are:

Reorganizing the database

Checking a tablespace before possibly extending it

Structure

Maximum number of extents per table or index – MAXEXTENTS

Soft limit

The SAP software value for MAXEXTENTS is 100.

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, we recommend you to change the extent parameters as follows so that the system can continue running:

Increase MAXEXTENTS .

Increase NEXT , so that the system does not reach the increased maximum number of extents in the near future. For more information on how to do this from the command line, see sapdba -next <tablespace(s)>.

Only reorganize the object in an emergency.

Hard limit

The usual hardware value for MAXEXTENTS is 505.

This limit only exists for Oracle releases before 7.3.

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 (see the table below). If you reach the hardware limit you must reorganize the affected table or index.

The following table shows the hard and soft limits for MAXEXTENTS by block size:

Block Size and MAXEXTENTS

Block size in KB	Hard limit for MAXEXTENTS	Soft limit for MAXEXTENTS
2	121	100
4	249	100
8	505	100

Note

The software value of MAXEXTENTS = 100 enables you to anticipate most cases where the number of extents is too high. This prevents you from reaching the hard limit and avoids reorganization.

Maximum number of files per database – DB_FILES

Soft limit

The SAP software value for DB_FILES is 254.

The database system only supports a specific number of data files in the database, depending on the host system, and this is specified by the DB_FILES parameter in the init<DBSID>.ora profile. If your database approaches this limit, you can reduce the number of data files by reorganizing tablespaces that have more than one file. However, this is not likely to occur with a limit of 254 files.

Hard limit

The hard limit for DB_FILES depends on the operating system but is at least 1022.

DB_FILES can be increased to the value of MAXDATAFILES , the value of which was specified when the database was created. MAXDATAFILES itself must be less than the permissible maximum number of open files supported by the operating system. The default value for MAXDATAFILES is also 254.

Caution

Do not regularly reorganize the database to reduce the number of data files. This is because the possible number of data files is large, so is not normally reached.