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 The bigfile
tablespace without ASM.
Bigfile Tablespace Benefits
Bigfile tablespace has the following
benefits:
- The bigfile tablespace simplifies large database
tablespace management by
reducing the number of datafiles
needed.
- The bigfile tablespace 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.
- The bigfile tablespace 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.
- The bigfile tablespace follows the concept that
a tablespace and a datafile are
logically equivalent.
Maximum Database Size
The bigfile tablespace 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 bigfile (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.
| BLOCK
SIZE |
MAXIMUM
DATA FILE SIZE |
MAXIMUM
DATABASE SIZE |
| 32 K |
128 GB |
8,388,608 GB
|
| 16 K |
64 GB |
4,194,304 GB
|
| 8 K |
32 GB |
2,097,152 GB
|
| 4 K |
16 GB |
1,048,579 GB
|
| 2 K |
8 GB |
524,288 GB |
Table 3.1: Maximum
database size in Oracle9i.
The maximum amount of data for a 32K
block size database is eight exabytes
(8,388,608 Terabytes) in Oracle 10g.
| BLOCK
SIZE |
MAXIMUM
DATA FILE SIZE |
MAXIMUM
DATABASE SIZE |
| 32 K |
131,072 GB |
8,589,934,592 GB
|
| 16 K |
65,536 GB |
4,294,967,296 GB
|
| 8 K |
32,768 GB |
2,147,483,648 GB
|
| 4 K |
16,384 GB |
1,073,741,824 GB
|
| 2 K |
8,192 GB |
536,870,912 GB
|
Table 3.2: Maximum
database size in Oracle 10g.
As you can see, with the new bigfile
tablespace
addressing scheme, Oracle 10g can contain
astronomical amounts of data within a single
database.With a
few exceptions, you need to use an alter
database resize datafile command to
resize a datafile.
One exception is the “bigfile
tablespace”.
Oracle will allows you to use the "alter
tablespace xxx resize datafile” syntax.
This is because you are not allowed to
add a datafile to a bigfile tablespace.
Remember, there is no alter tablespace
resize datafile syntax.
