Monday, February 8, 2016

What is big file tablespace in oracle?

A big file tablespace is a single tablespace can have  very large  - up to 4G blocks in a data file. As compare to traditional tablespaces which can contain multiple data files, but the files cannot be as large.

Some benefits of its are as under:

  • A Bigfile tablespace with 8K/16K/32K blocks can contain a 32/64/128 TB data file. 
  • The maximum number of datafiles in an Oracle Database is limited to Max. 64K files. 
  • A Bigfile tablespace can significantly improve the storage capacity of the Database.
  • Bigfile tablespaces reduce the number of data files for a database, with this DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted by DBA to reduce the amount of SGA space required for data files and size of the control file.
  • Bigfile tablespaces simplify database management by providing data file transparency instead of using multiple data files.
  • It supports only for locally managed tablespaces with automatic segment space management, and it’s not used for locally managed undo tablespaces, temporary tablespaces and SYSTEM tablespace.
  • It is advised to use big file tablespaces in a database where Automatic Storage Management (ASM) configured or other logical volume managers that support striping or RAID, and dynamically extensible logical volumes.
  • Avoid creating big file tablespaces on a database that is not supporting striping because of negative implications for parallel query execution and RMAN backup parallelization.


If the default tablespace type is specified to BIGFILE at a time of database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A big file tablespace is created by default.

CREATE BIGFILE TABLESPACE tbs_bigtable_ex1
DATAFILE '/u02/oracle/data/tbsbig01.dbf' SIZE 80G;

If the default tablespace type is specified to BIGFILE at a time of database creation, but you want to create a traditional - small file tablespace, then uses a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type which creating database.

CREATE SMALLFILE TABLESPACE tbs_smalltable_ex1
DATAFILE '/u02/oracle/data/tbssmall01.dbf' SIZE 80G;

No comments: