Difference Between Smallfile and Bigfile Tablespaces | Oracle Database

SMALLFILE Vs BIGFILE Tablespaces in Oracle Database | Which one to choose?

Oracle Database normally use the SMALLFILE tablespace which is also the By Default tablespace creation method. At the time of database creation, SYSTEM and SYSAUX are also created as smallfile tablespaces and, for the others getting created, will also be smallfile only if you don't specify BIGFILE clause while creating them.


So What is the difference between BIGFILE and SMALLFILE?

Oracle Database has provided the option of creating one single database file which can grow up to a very large size and thus helping in easier datafile management. With the growing number of datafiles, it becomes very tedious to manage them for DBAs specially in the cases of recovery scenarios or Refreshing/Cloning the database. That is the default scenario with smallfile tablespace which most of us still use.

Bigfile tablespaces use only one datafile that can extend itself upto 1024 times as compared to a Smallfile tablespace datafile. So if a smallfile datafile grows upto 32GBs in size, a Bigfile datafile can grow upto 32X1024=32768GBs in size. Pretty cool right !!

When to Use a Bigfile tablespace?

It's pretty clear that Bigfile tablespaces help in data transparency since every tablespace is having only one datafile so it seems very clear where our data can be. Also the database becomes more easy to manage since you have to manage less number of datafiles. So if you think you have that much of database size to manage, just go for it but before you do, there are also a few points to consider if you want to use Bigfile tablespaces in your database:

- Make sure you confirm the maximum file size limitations for the OS you are using.
- Bigfile tablespaces are designed to be used with ASM or any other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
- Make sure your system supports striping because if not, that can cause trouble in parallel execution and backup parallelization.
- Make sure that volume you are going to create bigfile tablespace is having enough size (since you can add only one datafile in one bigfile tablespace)
- Bigfile tablespaces help in performance increase but can take long time in restore and recovery operations.

To know more about these tablespaces - read here.

I hope this helps !!