今天在添加一个数据文件时报错:
SQL> alter tablespace HZ_SOAR_INDEX_DATA add datafile '+DATADG/soarhz/datafile/hz_soar_index_data01.dbf' size 10g autoextend on maxsize 32G;
alter tablespace HZ_SOAR_INDEX_DATA add datafile '+DATADG/soarhz/datafile/hz_soar_index_data01.dbf' size 10g autoextend on maxsize 32G
*
ERROR at line 1:
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range
然后查看自己数据库的db_block_size=8k,然后根据单个文件大小计算公式:
db_block_size * 4194303,数据块大小是8K,则单个表空文件最大是:8K*4194303=34359730176字节,换算成MB即32767.9921875MB,而32768MB是34359738368字节,所以对于数据块大小是8K的数据库而言,单个表空文件大小是的限制是小于32G而不是小于等于32G。终于明白为什么我想建32G的表空文件不能成功了,建32767MB的表空就可以了,修改脚本大小后成功建立表空。
SQL> alter tablespace HZ_SOAR_INDEX_DATA add datafile '+DATADG/soarhz/datafile/hz_soar_index_data01.dbf' size 10g autoextend on maxsize 30G;
Tablespace altered.
下面是根据不同的db_block_size,对应的maxsize datafile:
Oracle has limitation boundaries related with the db_block_size.
Depending the db_block_size you can create datafiles up to one specific size. For example:
db_block_size | Datafile upper limit
----------------------------------------------
2kb 8GB
4kb 16GB
8kb 32GB
16kb 64GB
32kb 128GB