Benefits and Considerations of Using Bigfile tablespaces

Benefits of Bigfile Tablespaces
•In a database there can have maximum 65533 data files. Database is limited by the datafiles to 65533. In other way we can say in a database there can have maximum 65533 tablespaces because each tablespace must include at least one file.

Smallfile tablespace can contain up to 1024 files, but bigfile tablespaces contain only single file that can be 1024 times larger than a smallfile tablespace. Since database is limited by the total tablespace 65533 or in other word by the total data files 65533 and since bigfile tablespace's datafile can be 1024 times larger than of smallfile tablespace maximum size of database can largely expanded if you use bigfile tablespace.

The detail calculation of overall database size with the bigfile tablespace and smallfile tablespace are discussed in Overall Database Size

•With the use of bigfile tablespace you can reduce the number of datafiles of a ultra large tablespace and thus we can manage the database well. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.

•If you use Oracle-managed files to name the datafile, bigfile tablespaces make datafiles completely transparent for users as bigfile tablespaces contain only one datafile.

Considerations with Bigfile Tablespaces
•We should consider to use bigfile tablespace with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.

•We should not use bigfile tablespace on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.

•We should not use bigfile tablespace if there is free space problem on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.

•We should not use bigfile tablespace on the system that impose limit to large file sizes.

•Overall, performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of smallfile tablespaces. Though in case of corrupted datafile restore time of the datafile is more than of smallfile tablespace case.

Related Documents
Overall Database Size