Alejandro Vargas' Blog

  • May 14, 2008

Bigfile Tablespaces

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

Bigfile Tablespaces may become a standard implementation on databases using high end storage and Oracle Automatic Storage Management.

In order to avoid potential performance issues Bigfile Tablespaces must being built on top of a file system that can grow up dynamically and that support file striping, both characteristics are native to ASM.

<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8"><title></title><meta name="GENERATOR" content="OpenOffice.org 2.3 (Linux)">This feature not only address the need of very large databases enabling datafile sizes of up to 4G blocks per tablespace of storage, the maximum database size using Bigfile tablespaces is 8 exabytes whith 32K blocksize.

Combined with Oracle Managed Files (OMF), Bigfile tablespaces makes datafile management completely transparent to the DBA, thus greatly simplifying small - medium and very large databases storage management.

The DBA will need to be aware and only check that enough free space is always available at the level of ASM DiskGroup, to support the natural growth pace of its applications.

Bigfile tablespaces can be implemented at database creation.

Using DBCA, you will need to edit the definition of each tablespace and check the 'use Bigfile' checkbox.


To add a Bigfile tablespace using OMF enter this command:

SQL> create bigfile tablespace dev;

Tablespace created.

------------------------------ ---------- --- ------
SYSTEM                              16384 YES MANUAL
SYSAUX                              16384 YES AUTO
UNDOTBS1                            16384 YES MANUAL
TEMP                                16384 YES MANUAL
USERS                               16384 YES AUTO
DEV                                 16384 YES AUTO

6 rows selected.

SQL> select bytes,file_name from dba_data_files where tablespace_name='DEV'




Join the discussion

Comments ( 1 )
  • odenysenko Thursday, May 29, 2008
    just keep in mind that until 11g
    RMAN is not able to restore single file
    using multiple channels, so for several TB
    tablespace you will need to wait until
    it will be restored through single channel...
    and you simply don't have any choices except
    switching to small file tablespace
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.