====== 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