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

Replace +DATADISK with your ASM disk:

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