Oracle Data File Management

Oracle Database Data Files (files with the extension of DBF) are files used to save data in the database, including system data, data dictionary data, temporary data, index data, and applications.

Oracle Database Data Files (files with the extension of DBF) are files used to save data in the database, including system data, data dictionary data, temporary data, index data, and applications.

Oracle Database Data Files (files with the extension of DBF) are files used to save data in the database, system data, data dictionary data, temporary data, index data, and application data are physically stored in data files. Operations on data in a database, such as data insertion, deletion, modification, and query, are essentially operations on data files. The size of the space occupied by all data files determines the size of the database capacity.

In Oracle databases, users do not directly operate data files to improve system operation efficiency, instead, the database process reads the data required by the user from the data file to the Data High-speed buffer cache, and then returns the data from the data high-speed buffer to the user, which improves the reusability of the system, reduce disk I/O. If you modify the data in the data high-speed buffer, the DBWR process writes dirty data to the data file under certain conditions. This "first memory and then external memory" method greatly improves the operating efficiency of the system.

In Oracle databases, although data files are attached to tablespaces, data files can be managed independently, this includes data file creation test, data file size modification, data file offline and online operations, data file name and location change, data file deletion, and data file information query.

An Oracle database contains a special data file, called a temporary data file, which is a temporary tablespace of the database. The content in the temporary data file is temporary and automatically released under certain conditions. In contrast to temporary files, other data files are called permanent data files.

In Oracle databases, data files are attached to tablespaces. A tablespace can contain one or more data files, but a data file can only belong to one tablespace. Logically, a database consists of tablespaces. Physically, a database consists of data files.

2. Data File Management Principles

The data file management rules mainly include reasonably setting the number of data files, the size of the data files, and the storage location of the data files.

2.1 determine the number of data files

2.2 determine the data file size

When creating a tablespace, estimate the potential size of the space required by the database object to determine the size of the data file. During database operation, you can add new data files or modify the size of data files to expand the tablespace as needed. When determining the size of the data file, consider the remaining disk space and usage.

2.3 set the storage location of data files

Because database operations are eventually converted to operations on data files, data files are frequently read and written during database operations. To improve I/O efficiency and eliminate competition, the storage location of data files should be allocated rationally.

1) place data files to be accessed in parallel on different hard disks to improve system read/write efficiency.

2) data files and control files. The redo log files are stored on different disks, which improves the running efficiency of the database and reduces the damage to the database when a media fault occurs. During database operation, the system needs to operate data files, control files, and redo log files in parallel, and distribute these physical files on different disks to achieve parallel operations and improve system operation efficiency.

3. Create a data file

In Oracle databases, data files are stored in tablespaces. Therefore, the process of creating data files is essentially the process of adding data files to tablespaces. You can create data files when creating databases and tablespaces, or add data files for tablespaces during database operation and maintenance. The number and size of data files should be determined based on the size of the database objects to be stored in the tablespace, the number of disks, and the remaining space.

4. Modify the data file size

4.1 automatic expansion of data files

In Oracle databases, the size of data files can be adjusted as the data capacity in the database changes. There are two ways to change the data file size: one is to set the automatic expansion mode of the data file, and the other is to reset the data file size.

4.2 manually change the data file size

In Oracle databases, You can manually modify the size of data files after they are created. If the database storage space is insufficient, you can increase the size of the data file instead of creating a new data file for the database, this is very effective when the number of data files reaches the limit of the database. If there is a large amount of data file space remaining, you can use the method of reducing data files to reclaim the storage space, to avoid the waste of storage space. The method of Manually changing the data file size is more beneficial to the expansion of large file tablespaces, because new data files cannot be added to large file tablespaces.

5. Change the name and location of the data file

After a data file is created, you can change its name and location. By renaming or moving data files, you can adjust the physical storage structure of the database without changing the logical storage structure of the database.

You can change the name and location of a data file at the tablespace level or at the database level. At the tablespace level, operations that change the name and location of data files only affect the applications of the current tablespace, and do not affect the applications of other tablespaces. At the database level, the database is loaded. When the database is not opened, all applications will stop.

6. delete data files

You can use the following statement to delete the specified empty data file in the tablespace.

Alter tablespace... drop datafile: Delete the NULL data file specified in the permanent TABLESPACE.

Alter tablespace... drop tempfile: Delete the empty temporary data file specified in the temporary TABLESPACE.

An empty data file or an empty temporary data file means that all zones allocated to the file are recycled. Colleagues who delete a data file or temporary data file will delete the information related to the data file in the control file and data dictionary, and delete the corresponding physical files in the operating system.

Deleting a data file or temporary data file is subject to the following constraints:

The database must be in the open status.

The data file must be empty. To delete a non-empty data file, you can delete the tablespace to which the data file belongs.

You cannot delete the first or only data file in a tablespace.

You cannot delete data files in read-only tablespaces that are migrated from the dictionary management mode to the local management mode.

You cannot delete data files in the SYSTEM tablespace.

You cannot delete offline data files in the local tablespace.