最近在维护数据库空间,给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博客 ” ,链接:http://blog.itpub.net/30776559/viewspace-2146790/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30776559/viewspace-2146790/