Specifying the Size for the New Data File (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Specifying the Size for the New Data File

Use

When you are using SAPDBA for Oracle to extend an existing tablespace or create a new tablespace – both of which involve adding a data file – you can specify a size for the new data file. SAPDBA suggests a size for the data file, but this section tells you how to specify a different size if required.

Prerequisites

SAPDBA calculates the suggested size of the new data file from the previous size of the tablespace using one of the following percentages:

Percentage from the SAPDBA profile ( init<DBSID>.dba ) if the parameter tspadd_tspname is set for this tablespace.

Default percentage, which is 30 percent of the tablespace size when no entry exists for this tablespace in the SAPDBA profile.

For example, you might want to specify a different size from the above values if you know that the tablespace might grow more rapidly in future.

Procedure

Make a new data file large enough to accommodate the growth of the tablespace for a long period. For a critical, rapidly growing tablespace, make the new file as large as possible. You also need to take the available storage space and the storage requirements of other tablespaces into account.

If you add many small files to a tablespace, you might later need to reorganize the tablespace. This can be extremely time-consuming. Also consider that you have to shut down the SAP System in order to reorganize the database. Reorganizing tablespaces with data files is the most complicated type of reorganization and should therefore be done as infrequently as possible. To avoid such situations, always add a sufficiently large file when extending a tablespace.

If possible, store I/O-intensive tablespaces each on separate hard disks.

If possible, select different mount points for a data tablespace and its index tablespace when you set up the database.

Caution

The SYSTEM tablespace can also be extended by SAPDBA. However, the number of data files for the SYSTEM tablespace cannot be reduced after you have extended it. Instead, you can use SAPDBA to resize data files, either reducing or increasing the file size.

A tablespace extension to PSAPTEMP can be cancelled by SAPDBA with the corresponding reorganization means. However, this is only possible if there are no temporary segments in PSAPTEMP (that is, you have to make sure that all other users are logged off).

You can undo a tablespace extension to PSAPROLL only by means of irregular procedures using Oracle functions. A thorough knowledge of Oracle procedures is required.