ORA-03206: maximum file size of XXXX blocks in AUTOEXTEND clause is out of range

If you are creating SMALLFILE datafile (default) with 8k blocks with datafile size greater than 32 GB than oracle will pop up with ORA-03206 error.

Because oracle will allow only up to 32 GB of datafile size limit with 8k blocks, maximum data blocks per datafile : 2^22 -1 = 4194303. So oracle simply won’t allow you in this case.

If you wish more than 32 GB datafile with 8k blocks than your datafile should be create with BIGFILE keyword.

Calculations are as follows:

Oracle allows max number of datafile is 65533 and Maximum data blocks per datafile is 2^22 -1 = 4194303 blocks.

Formula to calculate max datafile size

max datafile size = db_block_size * maximum number of blocks

In our case:

max datafile size = 8 * 4194303 = 33554424/1024 = 32767.9921875 MB/1024 = 31.99999237060547 GB, i.e. 32 GB

Formula to calculate Max tablespace size:

Max tablespace size = 32 GB * max database file = 32 GB * 65533 = 2097056 GB/1024 = 2047.90625 TB

Error Log:

SQL> create temporary tablespace temp02
tempfile '/u02/oradata/db1/temp2_01.dbf' size 30G autoextend on maxsize 70G; 2
create temporary tablespace temp02
*
ERROR at line 1:
ORA-03206: maximum file size of (9175040) blocks in AUTOEXTEND clause is out of range

Solution:

Create tempfile with less than 32 GB size or use BIGFILE keyword.

In above example, I am facing ORA-03206 with tempfile creation, you may face the problem with datafile too. Same solution will be applicable.

Other area’s you may get same error:

If your existing datafile exceeds limit of 32 GB, than you will receive same error message, solution is to add new SMALLFILE datafile to the associated tablespace.

Stay Tune. 🙂

Leave a Reply