Wednesday, January 8, 2020

ORA-3206 signalled during: alter tablespace MYTS add datafile

Getting an error while trying to add a datafile to the tablespace.

ORA-3206 signalled during: alter tablespace MYTS add datafile

SQL> alter tablespace MYTS add datafile '/data/db1/oradata/mydb/myts_02.dbf' size 100M autoextend on maxsize 65536M;
alter tablespace MYTS add datafile '/data/db1/oradata/mydb/myts_02.dbf' size 100M autoextend on maxsize 65536M
*
ERROR at line 1:
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range


SQL>



Notice that I am trying to add a datafile with 64G size. This is because my db_block_size = 16384


SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     16384
SQL>


If you look under
https://docs.oracle.com/cd/E11882_01/server.112/e40402/limits002.htm#REFRN0042

Smallfile (traditional) Tablespaces

Number of blocks

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.

222 = 4194304

If you look at the Oracle doc (Doc ID 804733.1)  then it is 4194303

which means that the value should be 
65535.98M

and command will look something like

alter tablespace MYTS add datafile '/data/db1/oradata/mydb/myts_02.dbf' size 100M autoextend on maxsize 65535.98M;


The work around is let oracle determine it and run command below without maxsize value.

SQL> alter tablespace MYTS add datafile '/data/db1/oradata/mydb/myts_02.dbf' size 100M autoextend on;

Tablespace altered.

SQL>











No comments: