Bigfile Tablespace

A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size.

Bigfile Tablespace Overview
The traditional tablespace is referred to as a smallfile tablespace (SFT). A smallfile tablespace contains multiple, relatively small files. The bigfile tablespace has the following characteristics:

• An Oracle database can contain both bigfile and smallfile tablespaces.
• System default is to create the traditional smallfile tablespace.
• The SYSTEM and SYSAUX tablespaces are always created using the system default type.
• Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.

There are two exceptions when bigfile tablespace segments are manually managed:

• Locally managed undo tablespace
• Temporary tablespace

Bigfile tablespaces are intended to be used with Automated Storage Management (ASM) (see Chapter 1) or other logical volume managers that support RAID.

However, you can also use it without ASM.

Bigfile Tablespace Benefits
Bigfile tablespace has the following benefits:

• It simplifies large database tablespace management by reducing the number of datafiles needed.
• It simplifies datafile management with Oracle-managed files and Automated Storage Management (ASM) by eliminating the need for adding new datafiles and dealing with multiple files.
• It allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database.
• It follows the concept that a tablespace and a datafile are logically equivalent.

Maximum Database Size

The BFT extended the maximum size of tablespace and database. Let’s take a look at the two formulas that calculate the maximum size of data file and database.

The maximum data file size is calculated by:

Maximum datafile size = db_block_size * maximum number of blocks

The maximum amount of data in an Oracle database is calculated by:

Maximum database size = maximum datafile size * maximum number of datafile

The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.

The maximum amount of data for a 32K block size database is eight petabytes (8,192 Terabytes) in Oracle9i.
The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.
As you can see, with the new BFT addressing scheme, Oracle 10g can contain astronomical amounts of data within a single database.