What is The Maximum Datafile Size Limit In Oracle Database 10gR2

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
Information in this document applies to any platform.

Goal:

What is the maximum datafile size in Oracle rdbms 10gR2?

There are two levels of restriction on the Oracle Datafiles:
1. By Oracle rdbms s/w.
2. By Operating System.

The maximum datafile size restriction will be decided by the one which is lower among the two mentioned above. This note talks about the file size limit imposed by Oracle rdbms s/w in 10gR2.

Solution:


Oracle s/w has actual restriction on the number of database blocks which can be allocated in a single datafile. So Maximum files size limit from Oracle side depends on the Database Block Size (DB_BLOCK_SIZE ).
Maximum number of Database blocks allowed in a single datafile in 10gR2 are as follows:

Small File Tablespace (Normal Tablespace)       : 4194303    (2^22 -1)
Big File Tablespace   (New in 10gR2)            : 4294967295 (2^32 -1)

Max datafile size for SMALL FILE NORMAL TABLESPACE would be:

Database 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

Max datafile size for BIG FILE TABLESPACE would be:

Database Block Size
Maximum Datafile 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

In Oracle Database 10g, a new type of tablespace is introduced, called a BIGFILE tablespace.

The BIGFILE syntax must be specified during the tablespace creation as such:
Create bigfile tablespace MY_TS

The limitation of a BIGFILE tablespace is that it can ONLY have a SINGLE file in a tablespace.
By creating a tablespace using the BIGFILE syntax, Oracle increases the maximum number of blocks in a datafile from the 4 Million blocks mentioned above to a maximum of 4 Billion.

Now final max datafile size will be determined by :
LOWER(Max file size limit of Oracle, Max file size limit of OS)

The OS file size limit typically depends on OS and filesystem.
Example WINDOWS 32 bit OS:
==========================
Maximum Database block size : 16,384 bytes or 16 kilobytes (KB)

Maximum OS file size for a FAT file    : 4 GB
Maximum OS file size for a NTFS file   : 16 Exabytes (EB)

So maximum database file size with 16k DB_BLOCK_SIZE on WINDOWS 32 bit OS
on FAT filesystem is     : LOWER (64 GB, 4 GB)   = 4GB
on NTFS filesystem is    : LOWER (64 GB, 16 EB)  = 64GB

Reference Note: What is The Maximum Datafile Size Limit In Oracle Database 10gR2 [ID 804733.1]

No comments:

Post a Comment