Bigfile tablespaces are tablespaces with a single large datafile (up to 4G blocks). In contrast normal or smallfile tablespaces can have several datafiles, but each is limited in size. The system default is to create a smallfile tablespace. The SYSTEM and SYSAUX tablespace types are always created using the system default type.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace
Advantages of using Bigfile Tablespaces:
By allowing tablespaces to have a single large datafile the total capacity of the database is increased. An Oracle database can have a maximum of 64,000 datafiles which limits its total capacity.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
Using fewer larger datafiles allows the DB_FILES and MAXDATAFILES parameters to be reduced, saving SGA and controlfile space.
It simplifies large database tablespace management by reducing the number of datafiles needed.
The ALTER TABLESPACE syntax has been updated to allow operations at the tablespace level, rather than datafile level.
Considerations:
Bigfile Tablespace can be used with ASM (Automatic Storage Management)
A logical volume manager supporting striping/RAID. Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.
Avoid using bigfile tablespaces if there could possibly be no free space available on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
Creating Bigfile Tablespace:
SQL > CREATE BIGFILE TABLESPACE DATAFILE ‘D:\oracle1\oradata\bigtbs01.dbf’ SIZE 50G
If the default tablespace type was set to BIGFILE at database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A bigfile tablespace is created by default.
If the default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify CREATE SMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.
Altering a Bigfile Tablespace:
ALTER TABLESPACE bigtbs RESIZE 80G;
With a bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD DATAFILE clause. For example:
ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
Note: An error is raised if you specify an ADD DATAFILE clause for a bigfile tablespace.
Views:
The following views contain a BIGFILE column that identifies a tablespace as a bigfile tablespace:
DBA_TABLESPACES
USER_TABLESPACES
V$TABLESPACE
