What is the Maximum Datafile Size Limit In an Oracle Database? [ID 804733.1]

		修改时间 16-MAY-2011 类型 HOWTO 状态 PUBLISHED

In this Document
Goal
Solution
From the Database Perspective
From the Operating System Perspective
References

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Goal

What is the maximum file size of an Oracle database datafile?

The datafile is restricted by the either the Oracle database or the operating system where the database exists. The value will be decided by which ever is the lower of the two.

Solution

From the Database Perspective

The Oracle database has a restriction on the number of database blocks which can be allocated in a single datafile. Therefore, the maximum file size limit depends on the database block size (DB_BLOCK_SIZE ).

Note: In Oracle Database 10g, a new type of tablespace was introduced called a BIGFILE tablespace.

The BIGFILE syntax must be specified during the tablespace creation as such: CREATE BIGFILE TABLESPACE my_ts;

The BIGFILE tablespace can ONLY have a SINGLE datafile. By creating a tablespace using this syntax, Oracle increases the maximum number of blocks in a datafile from the 4 Million blocks to a maximum of 4 Billion.

Refer to Oracle Database Reference 10g Release 2 (10.2)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits002.htm#i287915
Physical Database Limits

Example:
Maximum datafile size for a SmallFile Tablespace based on 10gR2 documentation would be:

Block Size Maximum Datafile File Size
----------- ---------------------------
2k 4194303 * 2k = 8 GB
4k 4194303 * 4k = 16 GB
8k 4194303 * 8k = 32 GB
16k 4194303 * 16k = 64 GB
32k 4194303 * 32k = 128 GB

Maximum datafile size for a BigFile Tablespace based on 10gR2 documentation would be:

Block Size Maximum Datafile File Size
----------- ---------------------------
2k 4294967295 * 2k = 8 TB
4k 4294967295 * 4k = 16 TB
8k 4294967295 * 8k = 32 TB
16k 4294967295 * 16k = 64 TB
32k 4294967295 * 32k = 128 TB

Refer to these sources to confirm settings for higher versions:

Oracle Database Reference
11g Release 1 (11.1)
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/limits002.htm#i287915
Physical Database Limits

Oracle Database Reference 11g Release 2 (11.2)
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/limits002.htm#REFRN0042
Physical Database Limits

From the Operating System Perspective

The file size limitation at the OS level is platform specific and the details are found in

-- Oracle Database Administrator's Reference for UNIX-Based Operating Systems
-- Oracle Database Platform Guide for Microsoft Windows

The documentation serves as a guideline and you should confirm the file size limitation with your OS vendor.

Example for Unix Platforms:

Oracle Database Administrator's Reference 10g Release 2 (10.2) for UNIX-Based Operating Systems
http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/appg_db_lmts.htm#sthref795

Table H-2 File Size Limits

Note: Release Notes for the specific platform should be reviewed for potential documentation issues.

Example:

Oracle Database Release Notes 10g Release 2 (10.2) for Linux x86
http://download.oracle.com/docs/cd/B19306_01/relnotes.102/b15659/toc.htm#BAJEEIHH

6.18 Incorrect Control File Size Limits
Table H-2, "File Size Limits" of Appendix H, "Database Limits" in Oracle Database Administrator's Reference for UNIX-Based Operating Systems Guide, states incorrect value of 20000 database blocks as control file size. The correct value is 25000 control file blocks with a block size of 4096 bytes.

Example for Windows Platforms:

Oracle Database Platform Guide 10g Release 2 (10.2) for Microsoft Windows (x64)
http://download.oracle.com/docs/cd/B19306_01/win.102/b15688/specs.htm#sthref626

Calculating Database Limits
Table 13-5 Maximum File Sizes

Refer to the respective Oracle on-line documentation for your platform and database version.

For ASM related file size restrictions please refer to the following note:

ASM - Scalability and Limits (Doc ID 370921.1)

References

Oracle Database Documentation Library 10g Release 2 (10.2)
http://www.oracle.com/pls/db102/homepage
Oracle Database Documentation Library 11g Release 1 (11.1)
http://www.oracle.com/pls/db111/homepage
Oracle Database Documentation Library 11g Release 2 (11.2)
http://www.oracle.com/pls/db112/homepage

显示相关信息 相关的

产品

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

关键字

LIMITATION; SIZING

返回页首

[@more@]