最近在维护数据库空间，给tablespace添加datafile的时候，由于达到了数据库最大datafile的个数，所以不能再添加。而我们的单个datafile的大小为2G，所以我就在想，单个datafile的大小限制到底是多少，我们可以相应扩大单个datafile的大小，以减少datafile的个数。

在网上参考了很多专业人士的技术文档，也做了相应的测试，现在记录如下：

1、创建普通tablespace时，报错

SYS@MTH> create tablespace TEST
2 datafile '/oradat/MTH/TEST01.dbf' size 2000M
3 autoextend on next 10M maxsize 50000M
4 extent management local
5 segment space management auto;
create tablespace TEST
*
ERROR at line 1:
ORA-03206: maximum file size of (6400000) blocks in AUTOEXTEND clause is out of range

2、Oracle官方相关限制设定

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 2^22 - 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on operating system and database creation options; maximum of 25,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022
Database files	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 2^22 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB
Redo Log File Size	Maximum Size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 2^32 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
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 (2^22) blocks.
External Tables file	Maximum size	Dependent on the operating system. An external table can be composed of multiple files.

每个datafile最多包含2^22-1个block,存在这个限制是因为Oracle的内部ROWID使用22位2进制数来存储不同的block号，所以22位最多代表(2^22-1)个block。

3、根据文档，计算我的数据库单个文件的大小限制

SYS@MTH> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

单个文件的最大大小：(2^22-1) × 8K=33554432K=32G

显然我指定的50000M超过了最大限制，所以报错。

查看到文档中建议单个datafile的大小设置成8G，既避免datafile过小导致的个数过多，难以维护，又能避免datafile过大，一旦出现问题很难恢复的情况。

ITPub博客

【Datafile】Oracle单个datafile大小的限制

最新文章