BigFiles Tablespaces, Normal Tablespaces and Tablespaces Sizes – How and When to use them

Until Oracle version 9i you normally only could create tablespaces with datafiles up to 32G (near that). They're called “Smallfile Tablespaces”. Particularly I prefer to call them “Normalfile”, because Small gives an impression of a size up to 2G or 4G. With version 10g and further, Oracle lets you create tablespaces with larger datafiles, up to 128T depending on the OS architecture (64 bits) and block size (32k).

 With a Smallfile Tablespace, normally the database administrator can create up to 1024 datafiles with 32G each – which gives nearly 34T of data – using 8K block size. We know that nowadays this amount of data isn't significant for some systems/companies, like Google, VISA, etc. Specially those which store videos, high resolution images and audio.

 A BigFile Tablespace can store more then three times a hole database that uses smallfiles, in a unique tablespace. Using a 32K block size, the database can reach 8 exabytes.  It's a huge amount of data. Unfortunately this can also bring some problem. As bigger is the datafile, as longer will be the time to restore its backup or do some maintenance on it.

 Some of those maintenance must bring the system offline depending on what is stored in the datafile. A good practice to avoid it, is to design your database in a way that you can divide tables that store large data (e.g.: Tables with BLOB or CLOB data) into a big file tablespace while the others in a normal tablespace. Or also you can partioned the table putting the less accessed data into the big file tablespace. In doing this, only a part of your system will be inaccessible during a maintenance.

 For example, in an Hospital Informatics System you can divide the tables which stores videos and images for diagnosis in a big file, while the stock, billing and administrative routines can stay in a normal tablespace, making that the system become available for those routines while only the diagnosis part will be offline for a maintenance.

On another hand there are Appliances (closed servers with a factory made configuration ready and directed for an specified needing) built to deal with those necessities, like BigData or ExaData, from Oracle, InfoSphere and Watson Foundations, from IBM, the CISCO USC and many others.

 So, is better you have a bunch of small soldiers, that move faster and from many sides, or one big and strong soldier, moving slowly and in a unique direction, in your army? All depends on your strategy!