BIGFILE TABLESPACE

Bigfile Tablespace.
If tablespace with 8k blocks can cantain a 32 tb datafile.
if tablespace with 32k blocks can cantain a 128 tb datafile.

Bigfile only supported Locally Managed tablespace with three exception.
Bigfile also supported below three tbs is extent management is local or segment space management is manual;

Undo Tbs
Temp Tbs + Extent Management LOCAL + Segment Space management MANUAL
SYSTEM tbs

Create Statement for Bigfile tablespace.
1.You have to specify "BIGFILE" keyword in create tablespace statement.
2.No need to specify "EXTENT MANAGEMENT" or "SEGMENT MANAGEMENT" clause.
3.If you specify extent management "DICTIONARY" or segment management "AUTO" database return error.
4.You can only specify one datafile.
5.If default tablespace type was set to "BIGFILE" at database creation time then no need to specify
"BIGFILE" keyword at create tablespace statement.
.But if you want to create smallfile datafile you have to specify "SMALLFILE" keyword at create tablespace statement

SQL> create BIGFILE tablespace BIGTBS
2 datafile 'c:\oracle\product\10.1.0\oradata\db02\bigtbs01.dbf' size 10m
3 autoextend on next 1m maxsize 50m
4 autoallocate;

Tablespace created.

SQL> select extent_management, segment_space_management
2 from dba_tablespaces
3 where tablespace_name = 'BIGTBS';

EXTENT_MAN SEGMEN
---------- ------
LOCAL AUTO