Three Reasons To Use Bigfile Tablespaces

Summer is almost over, kids go back to school, we go back to work, and settle back into our usual routine!

The blog posts are back as well!

I’d like to start with something easy and simple, but in the same time very useful: bigfile tablespaces!

Remember the days when the only option you had for a tablespace was a traditional tablespace? I believe that was prior to 10g. With the 10g version, bigfile tablespaces were introduced, and the traditional tablespaces started to be called smallfile tablespaces.

You might have taken some time before deciding to use bigfile tablespaces, or you might still not use them today…Maybe you are a big fan of bigfile tablespaces.
Either way, I’ll let you know the reasons to start using them!

Reason #1

Bigfile tablespaces only contain one datafile, and only one.
Why is this good? For starters, depending on the block size of the tablespace, the size of the datafile can grow up to 128TB.
More precisely, for an 8k block size, the datafile can grow up to 32TB, for a 32K block size, the datafile can grow up to 128TB.
By allowing such a big file size, you are no longer limited by a 32GB max file size on some operating systems.

Reason #2

Bigfile tablespaces reduce the number of datafiles that are needed for the database.
Let’s say you have a 350GB sized tablespace. Using a small file tablespace, you would need 11 datafiles for the tablespace to allocate that amount of space (assuming a maximum size of 32GB per datafile).
Using a bigfile tablespace you only need one datafile.
By reducing the total number of datafiles in the database, the size of the control files is also reduced, since less information needs to be stored in the control file.
You no longer need to manage tablespaces with tens of datafiles. Bigfile tablespaces really come in handy for large databases.

Reason #3

Managing operations on the datafiles of bigfile tablespaces, can now be accomplished by ALTER TABLESPACE statement, instead of ALTER DATABASE DATAFILE statement.
Since there is only one datafile in the tablespace, no need to explicitly specify the datafile. Oracle knows exactly which datafile to alter, since there is only one file in the tablespace.
This is my favorite reason to use bigfile tablespaces! If I need to extend a bigfile tablespace, or resize the tablespace, I do not need to query for the datafile name.
Instead of running 2 steps: 1) select datafile name, 2) alter database datafile…, I can just alter the tablespace! This is awesome!

See Reason #3 in action below:

Bigfile tablespace example:

SQL> create bigfile tablespace users_big datafile '+DATA' size 1G;
SQL> alter tablespace users_big resize 10G;

Smallfile tablespace example:

SQL> create tablespace users_small datafile '+DATA' size 1G;
SQL> select file_name from dba_data_files where tablespace_name='USERS_SMALL';
SQL> alter database datafile '+DATA/hrtst/datafile/users_small.2905.965107063' resize 10G;

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive

The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!