Oracle 数据文件 大小设置 说明

慧雅8254

先看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 是很通用的设法。 以后找到相关理论支持在补充。






------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：62697850 DBA 超级群：63306533;
聊天 群：40132017
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请