Thursday, August 30, 2012

Oracle datafile maximum size

To find out how big your datafile can grow, you should first know how much your OS/filesystem supports.

Maximum OS file size:
Maximum OS file size is determined by the type of filesystem where you are creating the file.
On Linux, type of filesystem can be determined using command df -T

e.g. ext3 filesystem supports maximum 2^32 blocks. (

Now how to find size of a OS block (Block size for a filesystem)?
On linux, it can be found using tune2fs or dumpe2fs.

dumpe2fs /dev/sda1 | grep -i 'Block size'
Block size:               4096


Maximum ext3 "filesystem" size with 4k blocksize can be 2^32*4k i.e. 16TB
Maximum size of single file created on that filesystem can be 2TB (Haven't figured out how is this limit calculated).

Oracle Database Maximum size for datafiles:
Reference: Oracle Documentation

It depends on:
1. Database block size (db_block_size)
2. Maximum database blocks per datafile. This is platform dependent.
3. Limited by maximum operating system file size

For smallfile tablespaces:
11gR2 : Operating system dependent. Limited by maximum operating system file size; typically 2^22(i.e. 4194304) or 4 MB blocks
i.e. for 8k db_block_size database, the maximum size of the datafile on ext3(4k OS block size) filesystem could be 32GB.

For bigfile tablespaces:
11gR2: Single datafile upto 2^32 blocks i.e. for 8k db_block_size, the maximum size of bigfile tablespace/datafile would be 32TB. But ext3(4k OS block size) file/filesystem would not support this big datafile. And it would be limited to 2TB for ext3 filesystem.