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>
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:
Post a Comment