联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
今天有朋友在群里面讨论oracle数据库最大可以存储的数据大小,下面根据官方文档提供的相关限制,大概估算出来oracle数据库最多可以存储的数据量
Physical Database Limits(11.2)
|
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 222 - 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 of25,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 222 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 ( 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. |
|
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 (222) blocks. |
|
External Tables file |
Maximum size |
Dependent on the operating system. An external table can be composed of multiple files. |
通过这里的相关限制可以大概的技术出来oracle数据库在传统数据文件和大数据文件情况下最大大小分别是:
传统数据文件(Smallfile)
32*1024(数据块大小)* (222–1)(一个数据文件的数据块数)*65533
(数据库中最多数据文件个数)= 9006784790495232(byte)/1024/1024/1024/1024=8191.6P
如果按照我们常用的block_size=8k,那么我们的数据库可以存储大小为2047.9P
大数据文件(Bigfile)
32*1024(数据块大小)* (232– 1)(一个数据文件的数据块数)*65533
(数据库中最多数据文件个数) = 9222949822242324480 (byte)/1024/1024/1024/1024 =8589541374P
如果按照我们常用的block_size=8k,那么我们的数据库可以存储大小为2147385343.5P
参考:http://docs.oracle.com/cd/E11882_01/server.112/e25513/limits002.htm
加我QQ
惜大，非常喜欢您的文章，受益良多。
今天自己算了一下大数据文件的容量，发现这篇在最后换算的时候出了一点问题：
9006784790495232(byte)/1024/1024/1024/1024=8191.6P
9222949822242324480 (byte)/1024/1024/1024/1024 =8589541374P
https://zh.wikipedia.org/wiki/国际单位前缀
k-M-G-T-P-E-Z-Y
在除四次1024后应该单位是T，所以小文件应该约是 8000TB，8PB
大文件9222949822242324480/1024/1024/1024/1024 =8388223TB，约为8EB
使用小文件单库可容纳8PB，大文件大1000倍，为8EB。