Shulou(Shulou.com)05/31 Report--

What is the limit on the size and number of data files in ORACLE? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

In ORACLE, a database is made up of instances and physical storage structures. The physical storage structure refers to the physical files stored on disk, including data files (data file), control files (control file), online redo logs (online redo log), parameter files (spfile/pfile), warning logs (alert log), tracking files (trace file) and many other files with different functions. The data we are most concerned with is stored in the data file (data file). So how do we plan the size and number of data files when creating and maintaining databases? There are more considerations involved. The main points are as follows:

1. Limitations of operating system

The database runs on the operating system, and the operating system is the foundation. therefore, the maximum file capacity and number that the operating system can support becomes the limit that the database can support. But this limitation also varies from operating system to operating system.

Here are some of the more common operating system restrictions on this:

WINDOWS

Most big data block: 16K

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

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

UNIX and LINUX

Most big data block: 32K (LINUX_X86 is 16K)

Maximum number of files: 65534

2. Restrictions on ORACLE database

Maximum number of files that can be managed per database: 65533

The maximum number of files that can be managed per tablespace: depending on the number of files that the operating system can open at the same time. Usually 1022.

Maximum capacity per data file: this value is equal to the block size * maximum number of blocks that can be managed

Among them, the maximum size of the data block is no more than 32K, and the general value is 8K; the number of manageable data blocks is 2 to the 22th power minus 1, which is about 4m blocks. Therefore, the maximum size of a data file with a block size of 8K cannot exceed 32G. However, if the maximum capacity of a single file supported by the operating system is less than this value, it is limited to the maximum capacity of the operating system.

3. Limitation of parameter DB_FILES

The parameter DB_FILES specifies the maximum number of files that an instance can create. This value can be modified, but it will not take effect until the instance is restarted. The DB_FILES setting is too low, which may cause the problem of not adding new data files. Setting it too high will consume more memory resources.

4. Performance and convenience impact

A) through careful design, the frequently accessed objects in the same tablespace are placed in different data files, and these data files are placed on different disk channels, which can improve the Icord O throughput.

B) put frequently changed data and unchanged data into different data files, and when backing up, only the changed data files can be backed up, thus reducing the backup and recovery time

Note: starting from ORACLE 10g, the technology of large tablespace is introduced. The so-called large tablespace is that the tablespace consists of only one data file. Its advantages are as follows:

1. The storage capacity is significantly increased. The number of manageable data blocks in large tablespaces is increased from 2 to 22 in traditional small tablespaces to 32 in 2. When the block size of 8K is also used, the maximum manageable space is 32T.

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

3. Simplify database management.

This is the answer to the question about the limit on the size and number of data files in ORACLE. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.