How to Resolve ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

  • by

ORA-03206

Theoretically, we can add a data file with maximum size of 32 GB to a tablespace with 8 KB block size. Actually, we can't.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example02.dbf' size 1G autoextend on next 1G maxsize 32G;
alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example02.dbf' size 1G autoextend on next 1G maxsize 32G
*
ERROR at line 1:
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

More precisely speaking, the size limit of a data file can be near 32 GB, but it cannot be exactly 32 GB. 32 GB for a smallfile tablespace with 8 KB db blocks is just an approximate and rounded figure, not the accurate one. The exact number should be additionally deducted two blocks which is 8 * 2 = 16 KB:

32 GB - 16 KB = 33554432 KB - 16 KB = 33554416 KB

In terms of blocks:

4194304 Blocks - 2 Blocks = 4194302 Blocks
4194302 Blocks * 8 KB per Block = 33554416 KB

That is to say, you should use the exact upper limit or less to define MAXSIZE of the data file.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example02.dbf' size 1G autoextend on next 1G maxsize 33554416K;

Tablespace altered.

There're more explanations and recommendations about size limit of a data file.

Leave a Reply

Your email address will not be published. Required fields are marked *