Show TOC

Procedure documentationManaging Data Files Locate this document in the navigation structure

 

You manage data files in your Oracle database as part of Space Management. You can resize and move data files and also set the AUTOEXTEND option.

This section discusses the approach to managing your data files.

For more information on how to manage your data files, see Altering a Data File with BR*Tools.

To avoid exceeding the limit for the maximum number of files, you must manage the data files in an Oracle database. To improve your database performance, you can move the data files to raw disks. To simplify database administration, you can move the files to the file system.

Prerequisites

The following constraints limit the maximum number of data files in an Oracle database:

  • The db_files parameter in the init<DBSID>.ora file is usually set to the value of the maxdatafiles option of the create database command. Currently, the SAP installation process sets maxdatafiles and db_files to 254. If you reach this limit, you can no longer add files to the database.

  • There is a UNIX kernel limit for the maximum number of open files.

  • There is an absolute Oracle maximum of 65533 files in a database and usually 1022 files in a tablespace. However, certain hardware platforms have a limit lower than this absolute maximum.

Procedure

  1. Resize a data file to provide more space for the objects in the file, as shown in the following graphic:

    This graphic is explained in the accompanying text.

    Make sure that you choose a sufficiently large size for the data file, allowing for future growth. Otherwise you might have to repeat the procedure soon.

    Use this method when you want to specify a larger size for the data file.

    For more information on how to resize a data file, see Altering a Data File with BR*Tools.

  2. Maintain the AUTOEXTEND option on a data file.

    With this option, the data file is extended automatically as the data grows. However, the entire disk can still overflow. Therefore, be sure to regularly monitor space on the disk volume.

    Use this method when you have enough space on the disk volume and the tablespace is not expected to grow too rapidly.

    For more information on how to maintain the autoextend option, see Altering a Data File with BR*Tools.

  3. You move data files, for example, when you want to relocate the data files to new disk drives for performance or other reasons. To improve your database performance, move the data files to raw disks. To simplify database administration, move the files to the file system.

  4. If you reach the limits specified in "Prerequisites" above, then do the following:

    • If you reach the limit for db_files or maxdatafiles, then do one of the following:

      • Increase the value of the db_files parameter, then shut down and restart the database.

      • Reorganize a tablespace consisting of several data files such that the number of files in use is reduced.

      • Recreate the control files specifying larger values for maxdatafiles.

      • Recreate the database specifying larger values for maxdatafiles

    • If you reach the UNIX kernel limit for the maximum number of open files, you have to change the relevant UNIX kernel parameter, then make sure that the change takes effect (such as shutting down and restarting the database, logging on again at UNIX level, restarting the UNIX system, and so on).

    • In the very unlikely event that you reach the hardware-dependent limit of files in the database (65533 files or less), then you have to perform a reorganization.