ORACLE最大可以存储多少数据量

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORACLE最大可以存储多少数据量

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

今天有朋友在群里面讨论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

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

ORACLE最大可以存储多少数据量》有 1 条评论

  1. Ghcost 说:

    惜大,非常喜欢您的文章,受益良多。
    今天自己算了一下大数据文件的容量,发现这篇在最后换算的时候出了一点问题:
    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。