Oracle 10g BigFile Tablespace.

One of the new features in oracle 10g is the introduction of 
bigfile tablespace.

A bigfile tablespace is a tablespace which basically consists of one
and only one datafile which can be as large as 128 Terabytes in size
depending upon the block size of the tablespace.

Following is the list of block sizes and maximum
sizes for bigfile tablespaces.

Tablespace Block Size Maximum Tablespace Size


2K   8TB

4K   16TB

8K   32TB

16K   64TB

32K   128TB


Creation Of bigfile tablespaces.


SQL>CREATE BIGFILE TABLESPACE big 
    DATAFILE '/u01/oracle/oradata/orcl/big.dbf' SIZE 10G
    autoextend on
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

The default tablespace type is SMALLFILE unless BIGFILE tablespace
clause is mentioned in the CREATE TABLESPACE command.

You can check the type of this tablespace in dba_tablespaces view.

SQL> select tablespace_name, bigfile from dba_tablespaces
     where tablespace_name='BIG';

TABLESPACE_NAME   BIGFILE
----------------  --------

BIG                YES



USAGE: Bigfile tablespaces is recommended to be used with
ASM or using volume Managers.