Alejandro Vargas' Blog

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.

 'bigfile-dbca'

To add a Bigfile tablespace using OMF enter this command:

SQL>  select TABLESPACE_NAME,BLOCK_SIZE,BIGFILE,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

------------------------------ ---------- --- ------
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'
     /
     BYTES 	FILE_NAME
---------- 	--------------------------------------------------
 104857600 	+DATADG/redpanda/datafile/dev.276.654704777