Bigfile Tablespace

posted Sep 9, 2010, 5:33 AM by Sachchida Ojha
Oracle 10g introduced a new type of tablespace called bigfile tablespace. Now  DBA can create a terabyte-sized datafile using the Bigfile option.A Bigfile Tablespace is a tablespace containing a single very large data file. A single bigfile tablespace file, either data or temp file, can be up to 128 terabytes for a 32K block tablespace and 32 terabytes for an 8K block tablespace. Bigfile tablespace contains only one file, whereas a traditional tablespace (smallfile type) can contain up to 1,022 files.

Bigfile tablespace simplifies database management with the ALTER TABLESPACE command to allow the operations at TABLESPACE level, which will help to modify the size and auto extend functionality for all of the datafiles in one shot, rather than doing at datafile level for each file. 

SQL > ALTER TABLESPACE TS_REPORTDATA RESIZE 100G;
SQL> ALTER TABLESPACE TS_REPORTDATA AUTOEXTEND ON NEXT 10G;

DBA should avoid using bigfile tablespace option if,

1. Bigfile  tablespaces should not be used on platforms with filesize restrictions, which
would limit tablespace capacity.

2. 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.

Comments