先看Oracle官网的说明,看的是Oracle 10gR2的文档:
Physical Database Limits
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. |
From:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/limits002.htm#REFRN0042
每个数据文件最多只能包含2^22-1个数据块。这个限制是由于Oracle的Rowid中使用22位来代表Block号,这22位最多只能代表2^22-1个数据块。这个限制也就直接导致了每个数据文件的最大允许大小。
在2K Block size下,数据文件最大只能达到约8G;
在8K Block size下,数据文件最大只能达到约4*8G;
在32K Block size下,数据文件最大只能达到约16*8G。.
A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) 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.
Bigfile tablespace只允许一个数据文件,在大文件表空间下,Oracle使用32位来代表Block号,每个文件最多可以容纳4G(2^32)个Block。那么也就是说当Block_size为2k时,数据文件可以达到8T。当Block_size为8k时,数据文件可以达到32T。当block_size为32K时,数据文件可以达到128T。
一般不建议把单个数据文件建的太大。因为太大的话,如果某个数据文件出现问题,恢复起来费时较长。
一般都是把单个数据文件设成8G一个,如果表空间较大的话,可以多弄几个数据文件。在上面的表格里也提到了。单个表空间最大支持1022个数据文件。所以数据文件是绝对够用的。
至于为什么设置8G,不是清楚,以前问过同事,说是和EXT3的文件系统有关系。刚才在metalink上也搜了一下,想找到相关的官方证据。搜了半天,没有搜到相关的理论支持。不过单个数据文件8G是很通用的设法。以后找到相关理论支持再补充。
------------------------------------------------------------------------------