An introduction to Oracle bigfile large file table space and analysis of its advantages and disadvantages

A Database is composed of one or more logical storage units called tablespace (tablespace). The logical storage units in the Tablespace are segments (segment), and segments can continue to be partitioned into data extensions (extent). The data extension is composed of a continuous set of data blocks (DataBlock).

Large File Table space

In Oracle, users can create large file table spaces (Bigfile tablespace). The table space (tablespace) used by the Oracle database can be composed of a single large file rather than several small data files. This enables Oracle to play a 64-bit system capable of creating and managing oversized files. In 64-bit systems, the storage capabilities of Oracle databases have been extended to 8 EB (1EB =1024PB,1PB = 1024TB,1TB=1024GB).

When a database file is managed by Oracle (oracle-managed files) and the large File table space (Bigfile tablespace) is used, the data file is completely transparent to the user. In other words, the user only has to perform administrative operations against the tablespace (tablespace) without caring for the underlying data file (datafile). Use large file table space to make tablespace a primary object for operations such as disk space management, backup, and recovery. With large file tablespaces and combined with Oracle managed database files (oracle-managed files) technology and automated storage management (Automatic Storage Management) technology, you no longer need It simplifies database file management by creating new data Files (datafile) manually and maintaining numerous database files.

The database defaults to creating a small file table space (Smallfile tablespace), the traditional tablespace (tablespace) type in Oracle. The SYSTEM and Sysaux tablespaces in the database are always created with the traditional type only locally managed (locally managed), and the tablespace of segment Space Automatic management (automatic segmentspace Management) (tablespace) To use large file tablespace (Bigfile tablespace). However, there are two exceptions: the locally managed undo tablespace (undo Tablespace) and the temporary tablespace (temporary tablespace), which can also use large file tablespaces, even if the segment (segment) is manual-managed (manually managed). An Oracle database can contain both large file/small file tablespace (Bigfile/smallfile tablespace). SQL statements are executed without regard to the type of tablespace (tablespace), unless the data file (datafile) name is explicitly referenced in the statement.

Administrators can create a set of temporary tablespace (temporary tablespace) that users can take advantage of in the space provided by each table space (tablespace) in the group. The administrator can also specify that the table space Group (tablespace Group) is the default temporary tablespace for the database. Large File table spaces and table space groups can be used when the user needs a large amount of temporary space for sorting operations.

Advantages of using large file table space

The use of large file table spaces (Bigfile tablespace) can significantly enhance the storage capabilities of Oracle databases. A small file tablespace (smallfile tablespace) can contain up to 1024 data files (datafile), and a large file tablespace contains only one file with a maximum capacity of 1024 times times the size of a small data file. In this way, the maximum capacity of the large file table space and the small file table space is the same. However, because each database uses up to 64K of data files, the maximum number of table spaces in a database is 1024 times times the size of a small file table space when using a large file tablespace, and the total database capacity when using large file tablespaces is three orders of magnitude higher than the use of small file table spaces. In other words, when an Oracle database uses a large file tablespace and uses the largest chunk capacity (32K), its total capacity can reach 8EB.

The use of large file tablespaces in very large databases reduces the number of data files and therefore simplifies the management of data files. As the data files are reduced, the information about the data files in the SGA and the capacity of the control file are reduced.

Since data files are transparent to the user, the database management process is simplified.

Factors to consider when using a large file table space

The large File table space (Bigfile tablespace) should be used in conjunction with automated storage management (Automatic Storage Management) or other logical volume management tools (logical Volume Manager). These tools should be able to support dynamic extended logical volumes, as well as striping (data across disk distribution) or RAID.

You should avoid using large file tablespaces on systems that do not support striping, because this would be detrimental to parallel execution (parallel execution) and RMAN parallel backups (backup

parallelization).

When the disk group that the tablespace is using may not have enough space, and the only way to extend the table space is to add data files to another disk group, avoid using large file tablespaces.

It is not recommended to use large file table space on platforms that do not support large files, which limits the capacity of the tablespace (tablespace). Refer to the relevant operating system documentation for the maximum file capacity it supports.

If you use a large file table space instead of a traditional table space, database open, checkpoints, and DBWR processes will improve performance. But increasing the data file

(datafile) capacity may increase backup and restore time.