Bigfile tablespace tips

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.

Rowids of Rows that belong to BFTs do not contain the relative file number.

Smallfile tablespace has a four-pieces format, OOOOOOFFFBBBBBBRRR, in which:
OOOOOO is the data object number of the segment.
FFF is the tablespace-relative datafile number of the datafile that contains the row.
BBBBBB is the data block that contains the row.
RRR is the slot number identifying the row inside a particular block.

Bigfile Tablespace Rowid Format

In a bigfile tablespace, there is only one file that always has a relative file number of 1024. A bigfile tablespace has a three-piece format, OOOOOOLLLLLLLLLRRR, in which LLLLLLLLL is used to denote the block number. The concatenation of FFF and BBBBBB makes the encoded block number.

For BFTs, the only supported way of getting components of extended rowids is to use the DBMS_ROWID package.

Here is an example on how to use the dbms_rowid package to retrieve rowid information:

select dbms_rowid.rowid_relative_fno(rowid, 'BIGFILE')
       bigfile_rowid,
       dbms_rowid.rowid_relative_fno(rowid, 'SMALLFILE')
       smallfile_rowid,
       first_name, last_name
FROM   hr.employees where rownum < 3;

Bigfile Tablespace Rowid Format

BIGFILE_ROWID SMALLFILE_ROWID FIRST_NAME           LAST_NAME
------------- --------------- -------------------- ----------
         1024               4 Mike                 Ault
         1024               4 Madhu               Tumma

Data Dictionary Views Enhancement

A new column is added to both dba_tablespaces and v$tablespace views to indicate whether a particular tablespace is bigfile or smallfile:

SQL> select name, bigfile
from v$tablespace;

NAME                           BIGFILE
------------------------------ -------
SYSTEM                         NO
UNDOTBS01                      NO
SYSAUX                         NO
TEMP                           NO
EXAMPLE                        NO
USERS                          NO
BIG_TBS                        YES

SQL> select tablespace_name,bigfile
     from   dba_tablespaces;

TABLESPACE_NAME                BIGFILE
------------------------------ ---------
SYSTEM                         SMALLFILE
UNDOTBS01                      SMALLFILE
SYSAUX                         SMALLFILE
TEMP                           SMALLFILE|
EXAMPLE                        SMALLFILE
USERS                          SMALLFILE
BIG_TBS01                      BIGFILE

Bigfile Tablespace Examples

Example 1: Create a database with default bigfile tablespace.

CREATE DATABASE GRID
SET DEFAULT BIGFILE TABLESPACE
DATAFILE '/u02/oradata/grid/system01.dbf' SIZE 500 M,
SYSAUX DATA FILE '/u02/oradata/grid/sysaux01.dbf' SIZE 500 M
DEFAULT TEMPORARY TABLESPACE tbs01
TEMPFILE '/u02/oradata/grid/temp01.dbf' SIZE 1024 M
UNDO TABLESPACE undo01
DATAFILE '/u02/oradata/grid/undo01.dbf' SIZE 1024 M;

Example 2: Moving data between smallfile and bigfile tablespaces.

ALTER TABLE employee MOVE TABLESPACE bigfile_tbs;

Example 3: Create a bigfile tablespace and change its size.

CREATE BIGFILE TABLESPACE user_tbs
DATAFILE '/u02/oradata/grid/user_tbs01.dbf' SIZE 1024 M;
ALTER TABLESPACE user_tbs RESIZE 10G;

In the previous release of Oracle server, K and M were used to specify storage size. Notice in this DDL statement, a user can specify size in gigabytes and terabytes using G and T respectively.

Example 4: Use DBVERIFY utility with bigfile. With small file tablespace, you can run multiple instances of DBVERIFY in parallel on multiple datafiles to speed up integrity checking for a tablespace. You can achieve integrity checking parallelism with BFTs by starting multiple instances of DBVERIFY on parts of the single big file.

$dbv FILE=bigfile01.dbf START=1 END=10000
$dbv FILE=bigfile01.dbf START=10001

Note: START = Start Block; END = End Block

Oracle Bigfile tablespace tips Oracle Database Tips by Donald Burleson