Oracle database physical data file limits and ORA-03206

Symptom:

SQL> create tablespace test
  2  datafile '/oradata/orcl/test01.dbf' size 100m autoextend on next 1m maxsize 40960m;
create tablespace test
*
ERROR at line 1:
ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range

Cause:

There is a limit for each database datafile's maximum size, without specifying BIGFILE keyword, the limit is 2^22 Blocks (which is 4 Million blocks), if the block size is 8k, the file limit will be 32768MB.

With BIGFILE keyword, the limitation is 4 billion blocks.

Examples:

SQL> create tablespace test
  2  datafile '/oradata/orcl/test01.dbf'  size 100m autoextend on next 1m maxsize 32768M;
create tablespace test
*
ERROR at line 1:
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

SQL> !  rm /oradata/orcl/test01.dbf

SQL> create tablespace test
  2  datafile '/oradata/orcl/test01.dbf'  size 100m autoextend on next 1m maxsize 32767M;

Tablespace created.

SQL> create tablespace test_new
  2  datafile '/oradata/test/test_new01.dbf' size 100m autoextend on next 1m maxsize 20480m,
  3  '/oradata/orcl/test_new02.dbf' size 100m autoextend on next 1m maxsize 20480m;

Tablespace created.