Restrictions on the size and quantity of data files in Oracle

In Oracle, databases are composed of instances and physical storage structures. The physical storage structure refers to the physical files stored on disks, including data files, control files, and online redo logs), parameter files (spfile/pfile), warning logs (alert log), trace files, and so on. The data we focus most on is stored in the data file. How can we plan the size and quantity of data files when creating and maintaining databases? This involves many considerations. There are mainly the following points:

1. Operating System Restrictions

The database runs on the operating system and the operating system is the foundation. Therefore, the maximum file capacity and quantity supported by the operating system are the limits that the database can support. However, this restriction varies with operating systems.

The following are some common operating system restrictions:

WINDOWS

Maximum data block: 16 K

Maximum number of files: 20000 (2 k data blocks)/40000 (4 K data blocks)/65536 (8 K or 16 K data blocks)

Maximum File capacity: 4 GB (when the file system is FAT)/64 GB (when the file system is NTFS)

UNIX and LINUX

Maximum data block: 32 K (16 K for LINUX_X86)

Maximum number of files: 65534

2. Restrictions on ORACLE databases

Maximum number of files per Database: 65533

Maximum number of files that can be managed for each tablespace: depends on the number of files that can be opened simultaneously by the operating system. Usually 1022.

Maximum capacity of each data file: The value is equal to the size of the data block * the maximum number of manageable data blocks

The data block size cannot exceed 32 K. Generally, the value is 8 K. The number of data blocks that can be managed is 22 to the power minus 1 of 2, which is approximately 4 m. Therefore, a data file with a data block size of 8 K cannot exceed 32 GB. However, if the maximum capacity of a single file supported by the operating system is smaller than this value, the maximum capacity of the operating system is limited.

3. Restrictions on the parameter DB_FILES

The DB_FILES parameter specifies the maximum number of files that an instance can create. This value can be modified, but it takes effect only after the instance is restarted. DB_FILES is too low to add new data files. If it is set too high, more memory resources will be consumed.

4. Impact of performance and convenience

A) carefully designed to place objects frequently accessed in the same tablespace in different data files and place these data files on different disk channels, i/O throughput can be improved.

B) place frequently changed data and unchanged data into different data files. During backup, only the changed data files can be backed up to reduce the backup and recovery time.

Note: large table space technology has been introduced since ORACLE 10 Gb. A large table space consists of only one data file. Its advantages are as follows:

1. Significantly increased storage capacity. The number of data blocks that can be managed in a large table space is increased from 22 to the power of 2 in a traditional small table space to 32. When 8 K data blocks are used for an hour, the maximum manageable space is 32 TB.

2. Reduce the number of data files required by the database.

3. Simplified database management.