Why Use Oracle Bigfile Tablespaces?

Oracle 10g (and continuing into 11g) introduced a new locally managed tablespace type for extreme-size databases: Bigfile tablespaces allow for the creation of tablespaces with one file where the size of that datafile fully incorporates the power of 64-bit systems. When implemented with Oracle Managed Files or Automatic Storage Management (ASM), bigfile tablespaces can greatly simplify the management of your storage system. Additionally, because you should have fewer datafiles, performance of database management operations such as checkpoints should improve, but be aware that recovery operation times are likely to increase in the event of datafile corruption.

Now you be may asking, “Then what is the benefit of bigfile tablespaces?” A bigfile tablespace with a typical 8K block can contain a single 32-terabyte datafile. If you’re using a 32K block, it can contain a 128-terabyte datafile. This is achieved by changing the way ROWIDs are managed within the tablespace. In a traditional tablespace, three positions in the ROWID are used to identify the relative file number of the row. Because you only have one datafile in bigfile tablespaces, these three positions are instead used to lengthen the data block number for the row, thereby allowing for a much larger number of ROWIDs from traditional smallfile tablespaces.

 

NOTE

To have the largest Oracle 11g database possible—8 exabytes—you must use 128T datafiles.

 

To use bigfile tablespaces, you must be using locally managed tablespaces with Automatic Segment Space Management (ASSM). Also, you cannot use bigfile tablespaces for UNDO, TEMP, or SYSTEM. If you are thinking of using bigfile tablespaces to reduce the amount of management needed for your system, consider also using Oracle Managed Files (OMF) and ASM. Also, if you are using traditional filesystems, make sure you are using a logical volume manager that provides the flexibility to map out your storage system appropriately so the single datafile can grow as needed.