This is an old revision of the document!

Create bigfile tablespace

BIGFILE tablespaces are useful for databases that require very large files, as they can theoretically accommodate up to 8 exabytes (EB) in size.

Syntax is:

CREATE BIGFILE TABLESPACE tablespace_name 
    DATAFILE 'file_location_and_name' SIZE file_size 
    [AUTOEXTEND ON [NEXT size_increment] [MAXSIZE { UNLIMITED | max_size }]]
    LOGGING | NOLOGGING
    EXTENT MANAGEMENT LOCAL 
    [SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }];

Example:

CREATE BIGFILE TABLESPACE big_ts 
    DATAFILE '/u01/app/oracle/oradata/mydb/big_ts01.dbf' SIZE 100G 
    AUTOEXTEND ON NEXT 10G MAXSIZE 500G 
    NOLOGGING 
    EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE "LARGETBS"
NOLOGGING
DATAFILE 'D:\ORACLE\LARGETBS' SIZE 51200K AUTOEXTEND ON

A bigfile tablespace contains only one data file or temp file. This is in contrast to SMALLFILE tablespaces, which can contain multiple files.

Create bigfile tablespace for ASM

CREATE BIGFILE TABLESPACE DATA 
DATAFILE '+DATADISK' 
SIZE 100G 
AUTOEXTEND ON 
NEXT 10G 
MAXSIZE 500G